Aaaarrrggghhhh excel formula - PLEASE HELP!!!

[sframe23]

Hello!!

I'm having some serious problems with excel formulas, my very limit3ed knowledge isn't exactly helping me today!

Basically I need to find a formula to count how many cells in E:E on my seperate sheet (called planned) contains the word CNF but not the words PCNF or TECO?

I was using the =COUNTIF(planned!E:E, "*CNF*") formula to count how many cells contained the word CNF but because the word PCNF contains CNF I couldnt get it to work and now I'm on the edge of totally giving up

Thankyou!!!!!

 

[JazzSP8]

Hi - Welcome to the board

Not sure if this will work for you but you could try

=COUNTIF(planned!E:E, "*CNF*")-COUNTIF(planned!E:E, "*PCNF*")

HTH

 

[sframe23]

Hi - Welcome to the board

Not sure if this will work for you but you could try

=COUNTIF(planned!E:E, "*CNF*")-COUNTIF(planned!E:E, "*PCNF*")

HTH

Wouldnt this just count the cells that contain the word CNF and PCNF?

Or am I still being an excel formula dummy!!

 

[sframe23]

Ahhhh yes I was being a formula dummy That works for part of it and I can edit it accordingly for my other cells!

Thankyou very much!!!

 

[bosco_yip]

Or this,

=SUM(COUNTIF(planned!E:E,{"*CNF*","*PCNF*"})*{1,-1})

Regards

 

[sframe23]

Thankyou for all your help! But now I have another problem I cant get my head around!

I now need a formula to count for how many cells in E:E ,on planned sheet, contain CNF but not PCNF or TECO but only when F:F , on planned sheet, contains FDM-4-M?

I'm about to lose my mind

 

[JazzSP8]

Hi

Sorry, say you said thanks and then thought that was you solved

Can only think of one way you could do that and it would need you to have a helper column that joins the two cells.

Your help column would combine the two cells, so for example if you used G:G then in G's cells you would put;

=E1&F1

Then you could amend the original formula I gave to;

=COUNTIF(planned!G:G, "*CNF*"&"FDM-4-M")-COUNTIF(planned!G:G, "*PCNF*"&"FDM-4-M")

There may be a better way to do it than that, if there is perhaps someone will see this bump and post back for you