PLEASE HELP - Cant seem to get formulas to work

[sframe23]

Hi

Really hoping someone can help me this is all sending me round the bend at the minute

I need a formula which can tell methe number of cells in E:E, on my seperate sheet called Bucket(R), which contain the word PCNF but do not contain the word TECO?

I tried =COUNTIF('Bucket(R)'!E:E, "*PCNF*")-COUNTIF('Bucket(R)'!E:E, "*TECO*") but this just subracts the numbers from each other and gives me an incorrect value!!

I also need a formula for the number of cells in E:E, again in Bucket(R), that contain CNF but not PCNF but only when F:F contains FDM-4-M?

I've almost finished all my work and just cant seem to get these two rigt, sorry it sounds soooooo confusing!!

Thankyou!!!!!!

 

[barry houdini]

Which version of Excel are you using? With 2007 or later you can use COUNTIFS (with as "S"), i.e.

=COUNTIFS('Bucket(R)'!E:E, "*PCNF*",'Bucket(R)'!E:E, "<>*TECO*")

=COUNTIFS('Bucket(R)'!E:E, "*CNF*",'Bucket(R)'!E:E, "<>*PCNF*",F:F,"*FDM-4-M*")

 

[sframe23]

No I don't think I am as those formulas aren't working for me, must be on an older version?!

 

[barry houdini]

OK, try SUMPRODUCT

=SUMPRODUCT(ISNUMBER(FIND("PCNF",'Bucket(R)'!E2:E100))*ISERR(FIND("TECO",'Bucket(R)'!E2:E100)))

 

[sframe23]

Right I've got the first part working, it's now just the part with the FDM-4-M I'm stuck with? Thankyou so much for your help! It seems to be taking forever!!

 

[barry houdini]

Try this version

=SUMPRODUCT(ISNUMBER(FIND("FDM-4-M",'Bucket(R)'!F2:F100))*ISNUMBER(FIND("CNF",'Bucket(R)'!E2:E100))*ISERR(FIND("PCNF",'Bucket(R)'!E2:E100)))

 

[sframe23]

Try this version

=SUMPRODUCT(ISNUMBER(FIND("FDM-4-M",'Bucket(R)'!F2:F100))*ISNUMBER(FIND("CNF",'Bucket(R)'!E2:E100))*ISERR(FIND("PCNF",'Bucket(R)'!E2:E100)))

Nope can't seem to get this one to work barry

I dont know what else to do I'm at a complete loss, I should have mentioned as well that PCNF and CNF are not the only words in the E:E cells but I have tried this formula with wildcards and still cant seem to get it to work!

Arrrrrggggghhhhh!!!!!!!

 

[sframe23]

Right I can now get that last one to at least give me something barry but its giving me the wrong numbers, basically I need it to find the cells that say REL CNF now instead of just CNF but when I try and modify it it doesn't seem to work?

Thankyou!!!!!

 

[barry houdini]

Can you post the formula you tried?

 

[shg]

Is this a different question than the one asked at http://www.mrexcel.com/forum/showthread.php?t=535793 ?