Shortcut for counting lots of items in multi-select column?

RAdamowicz

Hi all! I know how to do this manually but am hoping someone has a shortcut!

I have a sheet with a very long dropdown list of college majors for employees who have signed up for a particular program. It's a multi-select field since many people had more than one major, and they can also type in a response if their major isn't listed. I want to get a count of how many times each major appears in the grid. (i.e., there are 50 people in this program who listed at least one of their majors as Psychology). I know I can do a helper sheet and create a COUNTIF formula using the HAS function for each major, but as there are currently 317 majors in the sheet, that would be a massive time suck.

Does anyone have a brilliant idea for doing this without 317 separate formulas? (I really wish reports would break up and count multi-select fields, but no such luck!) Thanks in advance!

Georgie

Hi @RAdamowicz,

You could do this by copying the values from the Multi-Select column properties and then referencing each selection in your formula. 

To do this in the same sheet, take the following steps:

1. Create a new column called ‘Selections’.
2. Double click the multi-select column that you’re wanting to count values of (I’ll call it “Major”). Highlight all values in the values field and copy the selection.
3. Click OK/Cancel to close the column properties, then click the first cell in the Selections column and paste the values - these will be listed as one value per row.
4. In another column (your countif formula column), use the following formula:
   1. =COUNTIF(Major:Major, HAS(@cell, Selections@row))

The “Selections@row” section means that the formula will count the option listed in the row, so there’s no need to type out the name of each Major and adjust hundreds of formulas!

If you’d prefer to have your count in a separate sheet, take steps 1 and 2 above, then paste your values into a column named Selections on another sheet. In the formula column, you can then use the following formula, creating the {Major} reference as the entire ‘major’ multi-select column in the referenced sheet:

• =COUNTIF({Major}, HAS(@cell, Count@row))

Take a look at the following resources for more information:

• Create cross-sheet references
• Create efficient formulas with @cell and @row

Does that work for you?

Georgie

Laura D.

@RAdamowicz, I don't have it all worked out in my mind, but I believe you could make a report and group based on major. Then use the 'Summary' and add the count formula.

edit: just realized you said you already tried reports!

Maybe you could make a helper column next to your drop down column that is just =majorcolumn so it eliminates the drop down aspect

Georgie

Hi @RAdamowicz,

You could do this by copying the values from the Multi-Select column properties and then referencing each selection in your formula. 

To do this in the same sheet, take the following steps:

1. Create a new column called ‘Selections’.
2. Double click the multi-select column that you’re wanting to count values of (I’ll call it “Major”). Highlight all values in the values field and copy the selection.
3. Click OK/Cancel to close the column properties, then click the first cell in the Selections column and paste the values - these will be listed as one value per row.
4. In another column (your countif formula column), use the following formula:
   1. =COUNTIF(Major:Major, HAS(@cell, Selections@row))

The “Selections@row” section means that the formula will count the option listed in the row, so there’s no need to type out the name of each Major and adjust hundreds of formulas!

If you’d prefer to have your count in a separate sheet, take steps 1 and 2 above, then paste your values into a column named Selections on another sheet. In the formula column, you can then use the following formula, creating the {Major} reference as the entire ‘major’ multi-select column in the referenced sheet:

• =COUNTIF({Major}, HAS(@cell, Count@row))

Take a look at the following resources for more information:

• Create cross-sheet references
• Create efficient formulas with @cell and @row

Does that work for you?

Georgie

RAdamowicz

@Georgie that worked! Thank you so much!!

Georgie

@RAdamowicz,

Glad to hear it! Thanks for confirming it worked for you! ☺️

Georgie