#### sframe23

##### New Member
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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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*")

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

OK, try SUMPRODUCT

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

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

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

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

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

Can you post the formula you tried?

Replies
0
Views
572
Replies
0
Views
710
Replies
3
Views
1K
Replies
1
Views
354
Replies
2
Views
499

1,214,625
Messages
6,120,598
Members
448,973
Latest member
ksonnia

### 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.

### Which adblocker are you using?

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

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