COUNTIF from a multiple drop down, exclude a word using NOT

RogerR

Hi I'm trying to create a COUNTIF from a multiple drop down but run in to issues as following words are used “Not Accepted” and “Accepted”. I can count the “Not Accepted” by just using (CONTAINS(“Not” but I can’t count only “Accepted” as both “Not Accepted” and “Accepted” will be counted. I did try to use NOT(CONTAINS but without success. 

I get an error on following test. 

=COUNTIFS([RevShare Eligibility]1:[RevShare Eligibility]31; (CONTAINS("Accepted"; NOT(CONTAINS("Not"; @cell)))))

Paul Newcome

Nope. That was my fault.

In my region we use commas in between sections of formulas. Your region appears to use semicolons. I accidentally threw a comma in there between the two arguments for the AND statement.

Try this:

=COUNTIFS([RevShare Eligibility]1:[RevShare Eligibility]31; AND(NOT(CONTAINS("Not"; @cell)); CONTAINS("Accepted"; @cell)))

Paul Newcome

=COUNTIFS([RevShare Eligibility]1:[RevShare Eligibility]31; NOT(CONTAINS("Not", @cell)))

Give that a try.

RogerR

The formula you recommend count every cell, including blank cells. What I'm looking for is to count "Accepted" but not include "Not Accepted". I need somehow create a countif formula that counting cells that only include the word "Accepted" but not "Not Accepted" It's tricky. thanks a lot.

Paul Newcome

=COUNTIFS([RevShare Eligibility]1:[RevShare Eligibility]31; AND(NOT(CONTAINS("Not"; @cell)), CONTAINS("Accepted"; @cell)))

RogerR

Thanks. I get #UNPARSEABLE on that string but if I remove following, AND(NOT(CONTAINS("Not"; @cell)), it runs, so it's seems to be something in the AND(NOT(CONTAINS("Not"; @cell)), part that don't work properly

Paul Newcome

Nope. That was my fault.

In my region we use commas in between sections of formulas. Your region appears to use semicolons. I accidentally threw a comma in there between the two arguments for the AND statement.

Try this:

=COUNTIFS([RevShare Eligibility]1:[RevShare Eligibility]31; AND(NOT(CONTAINS("Not"; @cell)); CONTAINS("Accepted"; @cell)))