Countif for multiple selection column


I'm trying to find a formula that will count a particular option in a multiple select column on another sheet.

I've tried using CONTAIN, HAS and FIND but neither appear to work - I get an error or it just returns a zero, when I'm expecting 1. Any help would be greatly appreciated.




Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    The syntax is a little off. Try this...

    =COUNTIFS({File Review Range 4}, "1", {File Review Range 2}, "RE", {File Review Range 5}, OR(HAS(@cell, "Please save a pdf copy of the CMI report in the matter compliance folder"), HAS(@cell, "Failure to obtain the correct client identification information"), HAS(@cell, "Source of funds checks not completed"), HAS(@cell, "Source of wealth checks not completed"), HAS(@cell, "Conflict checks were not carried out on appropriate person")))

    A few things to note...

    The correct syntax for the HAS function in this particular instance is

    HAS(@cell, "specific text)

    Notice the comma instead of the equals?

    Also noticed that we need to close out each of the HAS functions before moving on to the next.

    Finally, we only need to put the OR in one time and wrap it around ALL of the options for that one range.

    OR(HAS(..........), HAS(..........), HAS(..........), HAS(..........))



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!