CountIF for a multi-select drop-down

Sarah Bird

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!

David Joyeuse

Hi @Sarah Bird

You're gonna need to use either FIND, HAS or CONTAINS here.

HAS is designed to work in Muti-Select 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!

David Joyeuse

@Sarah Bird

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!

Genevieve P.

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

David Joyeuse

Hi @Sarah Bird

You're gonna need to use either FIND, HAS or CONTAINS here.

HAS is designed to work in Muti-Select 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!

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

David Joyeuse

@Sarah Bird

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!

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.

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

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

Stephanie Lapera

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 multi-select 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