Trouble with COUNTIF CONTAINS Formula

n.oconnor

Hi there, I'm trying to build a summary sheet with formulas that COUNTIF cells in a column on another sheet CONTAIN a specific value (this column is multiselect dropdown).

I've come up with things like the following, but am still getting errors.

COUNTIFS({DCYF Impacts}:{DCYF Impacts}, CONTAINS("ASD - Administrative Services Division", @cell))

Where am I going wrong? To clarify, DCYF Impacts is the name of a column on another sheet, and "ASD - Administrative Services Division" is a possible value (multiselect) in that column on this other sheet.

Adam Murphy

I think this should work (you are defining the remote range incorrectly): COUNTIFS({DCYF Impacts}, CONTAINS("ASD - Administrative Services Division", @cell))

n.oconnor

Thanks @Adam Murphy. When I use that formula I get the #INVALID REF error. Both sheets are in the same workspace. Any other ideas on what I might be missing?

n.oconnor

I was able to solve this by the following formula. The only thing I changed was first referencing the other sheet and then selecting the range through the "link to another sheet" hyperlink offered.

=COUNTIFS({ASD Strategic Projects Intake Sheet Range 1}, CONTAINS("ASD - Administrative Services Division", @cell))

Adam Murphy

Glad you got it sorted, yes the formula is the same you just needed to define the cross-sheet reference as you did.