Countif with exception help?

Hello,

I am trying to make a spreadsheet that will count some values for me but not count cancelled items.

For example, if I have a list of:

red
blue
yellow
reddish
red-orange
red-cancelled
cancel-blue


And I want to count all the *red* items, but not count the cancelled red items, how do I do this?

If I only write =countif "red" it will not count the values like red-orange that I need

If I write =countif ("*red*") - countif ("*cancel*") then it will subtract the cancelled blue as well.

I cannot write =countif ("*red*") - countif ("red-cancelled"). This is for a large real data spreadsheet already in use, and people have been using both the words cancel and cancelled, and it may be at the beginning, middle or end of the phrase I need to negate.

I have tried using some forms of <>cancel but have not been successful myself.

Demo file should be attached.

Thank you for your time!

Hi & welcome to the board.
How about

Formula:

Please Login or Register  to view this content.

Okay, belt & braces approach - put this in E2:

=COUNTIF($A$2:$A$22,"*"&C2&"*")-COUNTIF($A$2:$A$22,"*"&C2&"*cancel*")-COUNTIF($A$2:$A$22,"*cancel*"&C2&"*")

then copy down.

Hope this helps.

Pete

Thank you, this worked perfectly!

You're welcome & thanks for the feedback