Countif not containing specific text

Heather K

I am trying to create a formula to count "Red" based on one column but not include certain ones which contain the text "Spring" in another column but I can't get it to work.

Originally I tried this formula:

=COUNTIFS([Aging]:[Aging], "Red", Project:Project, <> "Spring")

However, I realized that won't work because the Project column has variations of "Spring". For example Spring - Other, Spring - Supply, etc.  So how do I create the formula to not count all the ones that contain the word Spring?

Nic Larsen

Try this:

=COUNTIFS([Aging]:[Aging], "Red", Project:Project, NOT(FIND("spring", LOWER(@cell)) >0))

 

Heather K

That is still counting the ones that include Spring.

Nic Larsen

Hmm...strange. it is working properly on mine. See screenshots. Did you copy and paste the formula?

Heather K

I copy and pasted again and it is now working properly. I may have accidentally deleted something when I changed red to yellow and green in my other cells the first time. Thank you!

Nic Larsen

Perfect - glad its working now. 

Paul Newcome

As another variation (for future reference) you could also drop the NOT function and just say = 0 instead of NOT > 0

 

=COUNTIFS([Aging]:[Aging], "Red", Project:Project, FIND("spring", LOWER(@cell)) =0)

.

I will suggest maintaining the LOWER function though. Really makes life a lot easier when dealing with multiple variations of a word.

Nic Larsen

Good tip!

lmckaig96366

I am trying to do essentially the same thing but the formula doesn't seem to be working for me. I tried both suggestions, with "NOT" and without. The formula returns a value but it's the same value as if I'm counting only by my first criteria, it's not excluding my "Spring" (I'm actually trying to exclude cells that contain the letters CPS, but same thing here) I'm getting it to work by individually excluding every option that may include "CPS" by using <> but this is cumbersome and makes for a very large formula and leaves a lot of room for error. Any suggestions?

Andrée Starå

Hi @lmckaig96366

Can you maybe share the formula and some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

lmckaig96366

@Andrée Starå I've added some screen shots below.

this is how I was able able to do what I'm needing by excluding every possibility which, as you can see, is very busy and not very efficient. It returns a value of 445 which is the correct value not counting "CPS".

Here is where I try to use the formula from this thread. I get a value of 984 which is the total count, including "CPS".

I also try to use the second formula option in this thread and get the same value of 984.

lmckaig96366

@Andrée Starå I realized the first screen shot I included is the wrong formula. Same concept but this is what I'm using to exclude CPS.

Andrée Starå

@lmckaig96366

I'd be happy to take a quick look.

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

J.Barrow

@Andrée Starå,

I am having a similar issue and would love your help why its not working.

=COUNTIFS({Third Party Inspection Tracking Range 2}, $[Primary Column]@row, {Third Party Inspection Tracking Range 1}, [#1 - Non Compliance]$1, NOT(FIND ("Complete", LOWER({Third Party Inspection Tracking Range 3}))>0))

Tried it a few different ways based on your example but I cant get it to work.

Any help would be great.