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!!!!!!
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

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!!!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,600
Messages
5,597,114
Members
414,125
Latest member
iQQ

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