Countif for multiple selection column

Options
2»

Answers

  • Cheryl Collins
    Cheryl Collins ✭✭✭✭✭✭
    Options

    If I take the 2nd Range 5 out (just before the OR), I get an incorrect argument error. Range 5 is the multi-select corrective action I am trying to interrogate.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Right. So you should have Range 5 only listed once and all of the different options you want to search for in that range listed in the OR.


    Instead of

    =COUNTIFS({Range 5}, "Option A", {Range 5}, OR(HAS(@cell, "Option B"), HAS(@cell, "Option C"))


    You should have

    =COUNTIFS({Range 5}, OR(HAS(@cell, "Option A"), HAS(@cell, "Option B"), HAS(@cell, "Option C"))

  • Cheryl Collins
    Cheryl Collins ✭✭✭✭✭✭
    edited 08/24/21
    Options

    So, this s the formula I have now:

    =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", OR(HAS(@cell = "Failure to obtain the correct client identification information", OR(HAS(@cell = "Source of funds checks not completed", OR(HAS(@cell = "Source of wealth checks not completed", OR(HAS(@cell = "Conflict checks were not carried out on appropriate person")))))))))))

    But, it's still returning 10 instead of 1, so it looks like its counting all file reviews that have been done for RE rather than just the ones that have one of the options above.

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

    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(..........))

  • Cheryl Collins
    Cheryl Collins ✭✭✭✭✭✭
    Options

    Hi @Paul Newcome

    This has done the trick! Thanks so much for all your help on this one it was a complicated one in the end!! Thanks again

    Cheryl

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!