Trouble with COUNTIF CONTAINS Formula

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.

Answers

  • Adam Murphy
    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
    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
    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
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!