PLEASE HELP - Cant seem to get formulas to work

sframe23

New Member
Joined
Mar 14, 2011
Messages
9
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!!!!!!
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

New Member
Joined
Mar 14, 2011
Messages
9
No I don't think I am as those formulas aren't working for me, must be on an older version?!
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
OK, try SUMPRODUCT

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

sframe23

New Member
Joined
Mar 14, 2011
Messages
9

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

New Member
Joined
Mar 14, 2011
Messages
9

ADVERTISEMENT

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

New Member
Joined
Mar 14, 2011
Messages
9
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!!!!!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,863
Messages
5,834,046
Members
430,257
Latest member
Todor T

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top