COUNTSIFS does not Contain

RingJake

Hello,

I've put together the following formula, to count certain items in a different sheet that don't meet certain criteria, please see below.

=COUNTIFS({SE-Region}, Region@row, {Status}, <>"Completed", {Status}, <>"Canceled", {Status}, <>"Duplicate", {Summary}, <>CONTAINS("Onboarding", {Summary}))

The formula works until the "{Summary} ("Onboarding", {Summary})), so I'm not sure why this isn't taking.

The answer is outputting "0" to me and the formula is not taking, which I know is not true. Is there anything wrong with my syntax?

Paul Newcome

Try this...

=COUNTIFS({SE-Region}, Region@row, {Status}, <>"Completed", {Status}, <>"Canceled", {Status}, <>"Duplicate", {Summary}, NOT(CONTAINS("Onboarding", @cell)))

RingJake

Excellent, thank you!

RingJake

Hi @Paul Newcome, having difficulty with a very similar formula, but can't get it to work. Can you please see if there is any syntax that is off here as well?

The formula takes, but the count if 0 for everything I am applying this to.

=COUNTIFS({Carrier}, CONTAINS([Carrier Report Link]@row, {Carrier}), {Status}, <>"Completed", {Status}, <>"Canceled", {Status}, <>"Duplicate", {Summary}, NOT(CONTAINS("Onboarding", {Summary})))

Thank you!

Paul Newcome

You need to use "@cell" references within the range portion of the CONTAINS functions.

RingJake

Hi @Paul Newcome,

I've tried this but still do not get any responses. Is this what you mean?

=COUNTIFS({Carrier}, CONTAINS([Carrier Report Link]@row, {Carrier}), {Status}, <>"Completed", {Status}, <>"Canceled", {Status}, <>"Duplicate", {Summary}, NOT(CONTAINS("Onboarding", @cell)))

Paul Newcome

Yes, but you need that in each of the CONTAINS functions. Your first one still needs the @cell reference.

RingJake

Ah, I see. Perfect, thank you!

Paul Newcome

Happy to help. 👍️

Bek T

Hi @Paul Newcome I'm hoping you can help, I've a similar query…

I am trying to get a count of responses from names in one column ('complainant name', which I've set up successfully as a simple count to this point), to now not include those with a prefix of 'accrued' in the same column. I was trying the above so it was:

=COUNTIF(([Complainant Name]:[Complainant Name]), NOT(CONTAINS("ACCRUED"@CELL))

but I'm getting an unparseable error returned. It's probably something obvious - are you able to help please? Ngā mihi (thanks/regards)

Paul Newcome

@Bek T The parenthesis around the range are not needed. The error itself comes from inside the CONTAINS function though. You need a comma between the two pieces, and "@cell" is case sensitive (needs to be all lower case).

Bek T

Thanks Paul. My brain had got to a point where it went on strike from formulas, haha.

I've done the above and it works, however is now counting blank cells (which wasn't an issue prior to including the not contains). Any thoughts on a simple fix for that one? Cheers

Paul Newcome

You could use an AND function to also say "is not equal to blank".

=COUNTIFS([Column Name]:[Column Name], AND(NOT(CONTAINS("text", @cell)), @cell <> ""))

Bek T

Brilliant, thanks Paul, that works (and relieves my brain, not sure why I end up tackling formulas at the end of a busy day, ha!) Ngā mihi

Paul Newcome

Happy to help. 👍️