COUNTIFS and CONTAINS for at least one item in drop down

Options

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.

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    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
    Options

    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
    Kelly Moore ✭✭✭✭✭✭
    Options

    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
    Options

    HAS worked! Thanks again!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!