How do you count the number of times a drop down item is selected in multiselect column?

Michelle Underwood

We have a sheet with a column that allows multiselect of the drop down options. We need to count how many times an item has been selected but HAS only works if they select just the one option and CONTAINS grabs options that happen to appear inside other options. For example: Using CONTAINS({range},"apple") with a list that has apple and Snapple as options will count 13 Snapples and 23 apples even if there are only 10 apples. Using HAS({range},"apple"), if they select Snapple and anything else it won't find it at all.

We found a way to parse the multiselect so that each selection appears alone in a column, then query that grid, but they could theoretically choose over a dozen items off the list and we would need to add more than a dozen columns to parse into. The log sheet is already very, very wide and we don't want to add more columns to it. We tried to create a reference to the column in a metrics sheet and parse it there but can't figure out how to reference each row in the entire column without creating a direct reference to each cell, which would be repeated in each formula up to 3 times. (We discovered that the selections are separated by Char(10) line feed but only when multiples are selected.)

Option 1: Duplicate the column by reference into another sheet, then use formulas that reference that column in the same sheet. Is this possible? If so, how?

Option 2: Create a reference to the cross sheet column like in Excel $Sheet$Column1 where 1 is the row and increments as I copy the formula down the rows of the calling sheet or create a column in the Metrics sheet that exactly replicates the original. Then I can create a formula in each column referencing that cell in the row instead of making 13+ cross sheet references on each row.

Option 3: manually copy and paste the column contents every time we want to update the dashboard. This is not an ideal solution unless we can automate it and have a button on the dashboard/report to regenerate the statistics.

Option 4: Make the selections absolutely unique, such as add a period to the end of each one, and then go through and clean up the data that already exists. Not impossible but not ideal.

What would make this much easier is if there were a Parse() and ForEach() function so that we could create a single formula that would loop through each cell in a range, parse that cell content on the provided delimiter, then do CountIf on the array using HAS(). Since I know there isn't either one, what ideas can anyone suggest?

Michelle Underwood

@Lidiya Shutaya This is the format that the person I am helping originally tried but if one of the options is found within the text of another option, CONTAINS will not work correctly as you can see in the screen shot of the Metrics sheet. I had tried HAS but had not put the parameters in the correct order so I thought it wasn't working. It didn't give me any errors, just inconsistent results. Fixing the parameters fixed the problem and I hope this helps someone else who does the same thing.

Bassam Khalil

Hi @Michelle Underwood 

Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

Andrée Starå

Hi @Michelle Underwood

I hope you're well and safe!

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Michelle Underwood

I tried to be as detailed as possible because I am not the owner of the sheet, so I can't do any redaction or sharing, and the column in question on the sheet contains confidential information so redacting it would make it worthless as an example. I will see if I can create a sheet with a similar problem.

Michelle Underwood

Here is a Demo sheet that has a drop down list that is locked to preset entries only and allows multi-select. I will share with Bassam and Andree but I'm not sure how to make it public so anyone can look at it.

Demo - Smartsheet.com

I created a Metrics sheet and put in two counts: the first uses a reference to this Demo sheet Column 2; the second uses the range of columns named FIRST through ELEVENTH that parse out the entries that I copied from Demo and pasted into a column in Metrics. The screenshot doesn't show the parsing columns.

Metrics - Smartsheet.com

I hope that helps you figure out how to fix this.

Thanks,

Michelle

Michelle Underwood

I think HAS() is the answer after all. I didn't switch the parameters when I changed from CONTAINS to HAS so that it wasn't working correctly. That is why it found the cells that has the selection I was looking for as the first item in the cell but when it was a later selection.

Lidiya Shutaya

Hello @Michelle Underwood ,

Check out this thread, I think you may have had the same question/idea?

https://community.smartsheet.com/discussion/81470/count-how-many-times-a-multi-select-value-was-selected-in-a-cell-range-or-column#latest

Best,

Lidiya Shutaya

Michelle Underwood

@Lidiya Shutaya This is the format that the person I am helping originally tried but if one of the options is found within the text of another option, CONTAINS will not work correctly as you can see in the screen shot of the Metrics sheet. I had tried HAS but had not put the parameters in the correct order so I thought it wasn't working. It didn't give me any errors, just inconsistent results. Fixing the parameters fixed the problem and I hope this helps someone else who does the same thing.

Michelle Underwood

Since these sheets are on a trial account that will expire in 23 days, I am adding a better screen shot of the solution and why it works. For anyone who really wants to go the expanded columns route, I put the formulas below for the formula pattern where the 'Combined' row is a copy of the selected values from Demo and then moving to the right the columns are named FIRST, SECOND, THIRD, etc. until you have as many columns as there are possible options. As you can see if there are too many options in the drop down list your formulas will get too long for the cell limit, so this is impractical for a column with more than 30 possible selections, even if I somehow figured out how to get the cells in the Demo column without manually copying and pasting them over.

Here is the correct and easy solution using HAS() with the correct order of parameters:

In the header I had to abbreviate "[Primary Column]" to just "Primary".

The 'Entries Counted' is just a sum of the column and the 'Actual # Selected' is using =COUNTM({Demo Range 1}). The overcount is 'Actual # Selected' row minus the 'Entries Counted' row. I think that covers all the formulas in the Metrics sheet.

If you want to see what happens if you simply change Contains to Has you can recreate the problem and do that. You will see that it only counts the instances where the value in Primary Column appears as the first selection.

I hope this is helpful to someone.

Michelle

Formulas:

=IF(FIND(CHAR(10), Combined@row) > 0, LEFT(Combined@row, FIND(CHAR(10), Combined@row) - 1), Combined@row)

=IFERROR(IF(LEN(FIRST@row) > 0, LEFT(SUBSTITUTE($Combined@row + CHAR(10), $FIRST@row + CHAR(10), ""), FIND(CHAR(10), SUBSTITUTE($Combined@row + CHAR(10), $FIRST@row + CHAR(10), "")) - 1), ""), "")

=IFERROR(IF(LEN($SECOND@row) > 0, LEFT(SUBSTITUTE($Combined@row + CHAR(10), $FIRST@row + CHAR(10) + $SECOND@row + CHAR(10), ""), FIND(CHAR(10), SUBSTITUTE($Combined@row + CHAR(10), $FIRST@row + CHAR(10) + $SECOND@row + CHAR(10), "")) - 1), ""), "")

=IFERROR(IF(LEN(THIRD@row) > 0, LEFT(SUBSTITUTE($Combined@row + CHAR(10), $FIRST@row + CHAR(10) + $SECOND@row + CHAR(10) + THIRD@row + CHAR(10), ""), FIND(CHAR(10), SUBSTITUTE($Combined@row + CHAR(10), $FIRST@row + CHAR(10) + $SECOND@row + CHAR(10) + THIRD@row + CHAR(10), "")) - 1), ""), "")

=IFERROR(IF(LEN(FOURTH@row) > 0, LEFT(SUBSTITUTE($Combined@row + CHAR(10), $FIRST@row + CHAR(10) + $SECOND@row + CHAR(10) + THIRD@row + CHAR(10) + FOURTH@row + CHAR(10), ""), FIND(CHAR(10), SUBSTITUTE($Combined@row + CHAR(10), $FIRST@row + CHAR(10) + $SECOND@row + CHAR(10) + THIRD@row + CHAR(10) + FOURTH@row + CHAR(10), "")) - 1), ""), "")