Lookup/CountIF Formulas for Multi-Select ranges

Maria Watters

Hello,

We are referencing another sheet and are wanting to know how many times the word "SNAP" appears in the multi-select cells within a row. Right now, this is what we have, but it won't work. We have tried multiple different formulas as well and cannot seem to find anything to count the number of time "SNAP" shows up in the Focus Areas column.

Any help would be appreciated. Thank you in advance.

=COUNTIFS({Contacts with Demos Range 2}, [Focus Areas]:[Focus Areas], "SNAP"))

Maricel Medina

Try this

=COUNT(COLLECT({Contacts with Demos Range 2}, [Focus Areas]:[Focus Areas], HAS(@cell, "SNAP"))

Maria Watters

When I tried it, it came back as #unparseable

=COUNT(COLLECT({Contacts with Demos Range 2}, [Focus Areas]:[Focus Areas], HAS(@cell, "SNAP"))

Paul Newcome

Try

=COUNTIFS({Contacts with Demos Range 2}, "SNAP")

Maria Watters

When I do that one, I get a "0", which is incorrect, It should be showing a 1.

Paul Newcome

Double check you range to ensure that it is covering the correct column.

EDIT TO EXPLAIN:

When creating a cross sheet reference, sometimes it is possible to select the range before the sheet has had a chance to fully load. When that happens, the range will default back to the home cell (top left corner) after it does finally fully load. It happens to me sometimes, and it is easy to miss.

When creating the reference, let the sheet sit for a little bit before selecting your range.

Maria Watters

I checked and it is pulling the correct column.

Paul Newcome

Does the cell actually read "SNAP" or is it part of a string?

Maria Watters

So there are multiple items within the cell so "SNAP" is one of the selections

Paul Newcome

Ah. Ok. That is why it isn't picking it up. When you use free text ("SNAP"), then the cell must match that text to be counted. To find it within a string, there are a few different options. My suggestion for this particular case would be the CONTAINS function.

=COUNTIFS({Contacts with Demos Range 2}, CONTAINS("SNAP", @cell))

CONTAINS returns a true/false value if your text is found anywhere in the string. Not specifying whether to count for a true or false value means that it will default to true, so we are basically saying to count within the specified range any cell where the CONTAINS function returns a "true" by locating the text "SNAP".

Maria Watters

Is there a way to have a formula that counts the number of times "Snap" shows up in that column?

Paul Newcome

@Maria Watters Yes. The CONTAINS formula should work.