COUNTIFS and CONTAINS for at least one item in drop down

ialarson

I have a name drop down and and am trying to count the number of times my name shows in the Leader dropdown (by itself or with others) and keep getting UNPARSEABLE.

=COUNTIFS({CMK - Issue Type}, "Quick Hit", {Harvey Progress}, "<>Full", {Leader}, CONTAINS("Ingrid Larson"; @cell))

I'd appreciate any assistance, please.

Kelly Moore

Hey @ialarson

The Unparseable is caused by the semi colon in your CONTAINS function - or, depending on your country, the commas instead of semi colons everywhere else. Also, the 'not equal' is not enclosed with quotes, only the textstring you are excluding.

Your formula is either

=COUNTIFS({CMK - Issue Type}, "Quick Hit", {Harvey Progress}, <>"Full", {Leader}, CONTAINS("Ingrid Larson", @cell))

or

=COUNTIFS({CMK - Issue Type}; "Quick Hit"; {Harvey Progress}; <>"Full"; {Leader}; CONTAINS("Ingrid Larson"; @cell))

Will either of these work for you?

Kelly

ialarson

Yes! It needed the comma. Thank you. I couldn't even see it anymore I'd been looking at it so much.

But...now it's returning a zero for it, when I know there are some that qualify. If there are other items "checked" in the dropdown, besides my name, CONTAINS should still count it, right? Or do I need something different to count if it has my name by itself or with anyone else too?

Kelly Moore

Hey

Yes, CONTAINS should work. You can also try HAS. Note the syntax changes between CONTAINS and HAS

=COUNTIFS({CMK - Issue Type}, "Quick Hit", {Harvey Progress}, <>"Full", {Leader}, HAS(@cell,"Ingrid Larson"))

Assuming HAS doesn't work, let's find the culprit in the formula that is causing the return of 0.

First, one by one, completely delete the range in the formula and then, using the Insert Reference, re-insert your ranges. This will make sure that the correct column was originally inserted.

If that didn't fix the zero response, remove one range and criteria at a time. For instance, remove

, {Leader}, CONTAINS("Ingrid Larson", @cell)

Did you get a response other than zero? Continue eliminating range-criteria pairs until you get a non zero response.

Let me know what you get and we'll work through it

Kelly

ialarson

HAS worked! Thanks again!!