Note: Video demonstration at bottom of post.
In some applications, like Power BI, you can get a distinct count of unique values in a column in just a click or two. But of all the functions in Excel, there isn’t a straightforward “Distinct Count” unfortunately.
There are, however, the following two functions that, when used together, give the desired result:
- COUNTA – Counts the number of cells in a range that are not empty
- UNIQUE – Returns the unique values from a range or array
So, we’re listing all the unique values we have in a range or column by using UNIQUE and then nesting it within the COUNTA function to count the number of values returned. Here are three examples:
| Distinct count goal | Formula example |
|---|---|
| Distinct count for a range of cells | =COUNTA(UNIQUE(E19:E26)) |
| Distinct count for a table column when referenced within the same table | =COUNTA(UNIQUE([Project])) |
| Distinct count for a table column when referenced outside the table | =COUNTA(UNIQUE(Table1[Project])) |
Be sure to replace the cell range, column name, and/or table and column name in the formulas with your own.
A video demonstration of this formula in use is embedded below.