CountIF for a multiselect dropdown
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 multiselect 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

Hi @Sarah Bird
You're gonna need to use either FIND, HAS or CONTAINS here.
HAS is designed to work in MutiSelect dropdown columns so it's the best choice here:
=COUNTIF(Comments:Comments, HAS(Comments@row, "Requirement A"))
Others will be alike:
=COUNTIF(Comments:Comments, FIND(Comments@row, "Requirement A"))
=COUNTIF(Comments:Comments, CONTAINS("Requirement A",Comments@row))
CONTAINS and FIND will look for strings of characters so both should work as well, HAS will specificly look for the item "Requirement A"
Hope it helped!

The "@row" is replacing the row number and refers to the actual row. It's better to use @row that using numbers as a general rule, except if you want to point to a specific cell. In which case it's better to use the "$" argument prior to the row number to make sure it doesn't change when dragging down the formula or whatever.
such as:
=FIND(Comments$3, "Requirement A")
That one would check if "Requirement A" is present in cell row 3 of the Comments column.
Hope it helped!

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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Answers

Hi @Sarah Bird
You're gonna need to use either FIND, HAS or CONTAINS here.
HAS is designed to work in MutiSelect dropdown columns so it's the best choice here:
=COUNTIF(Comments:Comments, HAS(Comments@row, "Requirement A"))
Others will be alike:
=COUNTIF(Comments:Comments, FIND(Comments@row, "Requirement A"))
=COUNTIF(Comments:Comments, CONTAINS("Requirement A",Comments@row))
CONTAINS and FIND will look for strings of characters so both should work as well, HAS will specificly look for the item "Requirement A"
Hope it helped!

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

The "@row" is replacing the row number and refers to the actual row. It's better to use @row that using numbers as a general rule, except if you want to point to a specific cell. In which case it's better to use the "$" argument prior to the row number to make sure it doesn't change when dragging down the formula or whatever.
such as:
=FIND(Comments$3, "Requirement A")
That one would check if "Requirement A" is present in cell row 3 of the Comments column.
Hope it helped!

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"))

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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
That did the trick, thanks, Genevieve! HAS is a very useful function.

Edit: I was able to figure it out.
=COUNTIF(Applied:Applied, HAS(@cell, "Internship Name"))
I kept putting the column name with @cell which was giving me an error but only using @cell worked.
I am trying to count how many students applied to a specific internship and in some cases they applied to more than one internship.
I have an applied column as a multiselect type with the different internship locations as options. I am using sheet summary to tally the information. Can the HAS function be used in a sheet summary?
For single selections in the applied column the following function works great.
=Countif(applied:applied, "Internship Name")
For 2 or more selections in the applied column, neither works.
=COUNTIF(Applied:Applied, HAS(Applied:Applied, "Internship Name"))
I just a 0 but no error but I know the internship name has been selected at least 1.
=COUNTIF(Applied:Applied, HAS(Applied@row, "Internship Name"))
I get unparseable with the 2nd function which I am not surprised that I can't use @row in sheet summary.
Any help would be appreciated. Thanks
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!