CountIF for a multi-select drop-down

Hello,

I'm using a column to track what each row requires as a next step in the project. In some cases a row will need both say "requirement A" and "requirement B", therefore I'm using a multi-select dropdown because it makes the most sense for me.

In the sheet summary tab, I want to be able to count how many rows need "requirement A" and how many rows require "requirement B" and so on for other requirements in the dropdown.

Right now I'm using a CountIF formula such as: =COUNTIF(Comments:Comments, "Requirement A") - where "comments" is the column with these requirements. However, this formula disregards any rows where more than one dropdown is selected.

I want to be able to count rows more than once: i.e. if it has both Requirement A and Requirement B selected, it would be counted both times when I make summary cells for how many rows need requirement A and how many rows need requirement B.

Is there any way to do this? I appreciate the help in advance!

Best Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Megan P

    Try using @cell in your HAS function instead of the cell to the left. You're searching the entire column ([Sections Affected]:[Sections Affected]) so you want to see if any of the cells in that column (@cell) have your value:

    =COUNTIF([Sections Affected]:[Sections Affected], HAS(@cell, "Tracking"))

    You'll also want to ensure that the column name is spelled exactly the same as in your sheet.

    Cheers,

    Genevieve

Answers

  • Sarah Bird
    Sarah Bird ✭✭✭

    Hi @David Joyeuse

    Thanks for the guidance, it's greatly appreciated. Quick question though, what does that "@row" represent in your formulas? I'm trying to count from all the rows available on my sheet

  • Megan P
    Megan P ✭✭

    I have tried using this formula in a sheet where I have a similar goal, but it continues to return #UNPARSEABLE. Is there anything incorrect about this? =COUNTIF([Sections Affected]:[Sections Affected], HAS([Sections Affected]@row, "Tracking"))

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Megan P

    Try using @cell in your HAS function instead of the cell to the left. You're searching the entire column ([Sections Affected]:[Sections Affected]) so you want to see if any of the cells in that column (@cell) have your value:

    =COUNTIF([Sections Affected]:[Sections Affected], HAS(@cell, "Tracking"))

    You'll also want to ensure that the column name is spelled exactly the same as in your sheet.

    Cheers,

    Genevieve

  • Megan P
    Megan P ✭✭

    That did the trick, thanks, Genevieve! HAS is a very useful function.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!