Syntax
DISTINCT(range)
- range—Represents the group of cells to evaluate. You can include one or more columns.
Sample usage
COUNT(DISTINCT([Created By]:[Created By]))
Usage notes
DISTINCT is a helper function, and can’t be used independently. It replaces the range within another function or formula such as COUNT or JOIN.
For DISTINCT to assess them, all values must be of the same data type (numeric, text, dates, etc.). You will get an INVALID DATA TYPE error when you reference a range with text and numeric values. A cell containing mixed text and number characters, such as Part 157, is considered text and can be assessed using DISTINT.
To ensure that DISTINCT only references one data type, consider using COLLECT:
=COUNT(DISTINCT(COLLECT({Range 1}, {Range 1}, ISNUMBER(@cell))))
Examples
Example 1
COUNT(DISTINCT([Created By]:[Created By]))
This function determines how many unique individuals submitted forms to populate data within the sheet.
Example 2
=JOIN(DISTINCT([In Stock Item]:[In Stock Item]), ", ")
This formula combines unique values from the In Stock Item column into a single string separated by commas.
If you want your results to appear as a stacked list within a cell when using the Text wrap option or as a list in a multi-select field, add the CHAR(10) delimiter to the formula:
=JOIN(DISTINCT([In Stock Item]:[In Stock Item]), CHAR(10))
Example 3
INDEX(DISTINCT([Created By]:[Created By]), Sequence@row)
Use this with a manually numbered sequence value to list all unique values found within a range as a table.
Include IFERROR if the total number of unique values may be fewer than the total sequence values added.
Still need help?
Use the Formula Handbook template to find more support resources, view 100+ formulas, review a glossary of every function that you can practice working with in real time, and find examples of commonly used and advanced formulas.
You can find examples of how other Smartsheet customers use this function or ask about your specific use case in the Smartsheet online Community.