Counting Multi Select drop downs

Susan van Niekerk

Hi Brains Trust

I have a sheet with two multi select drop downs - we need a count of all the selects within that row - any ideas on how to do that - every thing i have tried doesn't work

The other questions is it possible to build a formula that would count specific selections within a multiple selection

Cant use PASE as there are around 15 different options and they dont want different columns - basically looking to pull counts of how many specific statuses with each of the Procedural and action Task columns, and they dont want to create extra row per status

Paul Newcome

You could just use the COUNTM function which is designed specifically for counting how many selections were made in a multi-select dropdown.

=COUNTM([Column Name]@row)

Not sure I follow your secind ask though. Are you wanting to count how many times a specific selection is made within a multi-select column?

If so, that is what the HAS function was designed for.

=COUNTIFS([Column Name]:[Column Name], HAS(@cell, "Specific Selection"))

Paul Newcome

@Susan van Niekerk I still stand by my suggestions of the COUNTM function to get total selections made and then the COUNTIFS with a HAS to count specific selections made. Is that what you ended up using?

Susan van Niekerk

@Paul Newcome

hi Paul

Yes used COUNTM for all my overall counts and then COUNTIFS & HAS to bring across the counts of specifics Statuses.. worked a treat!

Naeem Ejaz

Use the formula below to count how many items are selected in a multi-select dropdown:

t=LEN([Dropdown Column]@row) - LEN(SUBSTITUTE([Dropdown Column]@row, CHAR(10), "")) + 1

• CHAR(10) is the line break character used between selections in a multi-select field.
• LEN(...) gets the number of characters in the cell.
• SUBSTITUTE(..., CHAR(10), "") removes line breaks, and comparing the length before and after tells you how many line breaks (i.e., how many selections).
• Add +1 because the number of items is always one more than the number of line breaks.

Naeem Ejaz

=LEN([Dropdown Column]@row) - LEN(SUBSTITUTE([Dropdown Column]@row, CHAR(10), "")) + 1

Paul Newcome

You could just use the COUNTM function which is designed specifically for counting how many selections were made in a multi-select dropdown.

=COUNTM([Column Name]@row)

Not sure I follow your secind ask though. Are you wanting to count how many times a specific selection is made within a multi-select column?

If so, that is what the HAS function was designed for.

=COUNTIFS([Column Name]:[Column Name], HAS(@cell, "Specific Selection"))

Susan van Niekerk

Hi Paul

First part is counting the total number of status of the two red coumns( of which there could be up 4 multi selects per cell - so instead of return of 1, I would need to count all the ones within the cell so I need a return of lets say for this example 4

So how many Procedural tasks do we have in total ( Including any mutli selected options)

the second part is - we need to count how many for example "MOC Orders -Draft" tasks are in the column (There are around 15 options)

Thanks

SSFeatures

Hi @Susan van Niekerk, I think Paul is spot on with his formulas above.

This formula will give you the total number of selected options in the "Procedural Tasks" column, for the given row:

=COUNTM([Procedural Tasks]@row)

If you want a formula that will give you the total number of selected options in the "Procedural Tasks" column and the "Administration Tasks" column, you can try this one:

=COUNTM([Procedural Tasks]@row, [Administration Tasks]@row)

To count how many times "MOC Orders - Draft" appears in the "Procedural Tasks" column, you can try this formula:

=COUNTIFS([Procedural Tasks]:[Procedural Tasks], HAS(@cell, "MOC Orders - Draft"))

Hope this helps!

Susan van Niekerk

Hi @SSFeatures-

my return is 0 even though I have 7 in the column - what am i doing wrong?

Susan van Niekerk

@SSFeatures

Got it working - Thanks to all

Paul Newcome

@Susan van Niekerk I still stand by my suggestions of the COUNTM function to get total selections made and then the COUNTIFS with a HAS to count specific selections made. Is that what you ended up using?

Susan van Niekerk

@Paul Newcome

hi Paul

Yes used COUNTM for all my overall counts and then COUNTIFS & HAS to bring across the counts of specifics Statuses.. worked a treat!

Paul Newcome

Excellent! Happy to help. 👍️