How do I count unique values in a column?

Antonio Mcfarlane

Good day,

I need to count the number of unique values in a column, and I'm unable to come upon the correct formula/approach. I have multiple Incidents assigned to several reports which make it very difficult to report on the number of incidents overall because:

1. With a multi value list, where more than one incident is assigned in a cell, Count only counts 1. CountM counts multiple in a cell, but CountM(Distinct) doesn't account for duplicates in the column)

2. When I use a regular list of values, if a particular incident affects more than one report, it's counted multiple times because the Count(Distinct) refers to the total contents of the cell.

I've tried Count, Count Distinct, CountM (on a column where the values are formatted as Multi-select values) and a parsing formula from a Smartsheet forum discussion.

The issue centers around being able to count multiple values in a cell, and discount duplicates in the column.

Is there a way to define an array, or pull/parse these into another sheet to count them?

Thanks!!

Brian Connell

We do similar things through an expansion of all values into rows in another sheet. Requires a running cumulative count of elements column that takes the prior row total and adds the current row element count to that total.

You run an index in the expansion sheet, collect all elements in a sheet summary field where you join/separate with a line break/CHAR(10), and then pull them row by row into the expanded rows via another complex formula parses through that text to remove previously expanded rows and then selects the value for the next/current row.

Some pain expected in maintenance if you have others interacting frequently with these sheets. Also if your values are high LEN() you will hit that 4000 character limit in the collection cell where you're joining everything. Had to change a "N/A" option to "-" just to save the characters and keep it functioning.

Brian Connell

If your population of multi-select element values is known or predictable, I would take a simpler approach of listing all values in a separate sheet/table, run a countif formula, and then do a count on the rows in which that value is higher than 0, i.e. true that it has been listed as an incident in 1 or more rows.

Based on your example data though, might be tough if Incident numbers don't have consistent UID format.