How do I count distinct unique values in an Excel list?

Use this array formula (press Ctrl+Shift+Enter): =SUM(1/COUNTIF(range, range)). It divides 1 by the count of each item, so duplicates contribute a fraction that sums to 1.

Can I count unique values in Excel 365 without an array formula?

Yes - Excel 365 includes the UNIQUE() function. Use =COUNTA(UNIQUE(range)) to count distinct values without Ctrl+Shift+Enter.

Why does the array formula =SUM(1/COUNTIF()) return an error?

If the range contains blank cells, COUNTIF returns 0 for them, causing a division-by-zero error. Wrap with IFERROR: =SUM(IFERROR(1/COUNTIF(range,range),0)).

Count Unique Values in Excel List

MS Excel has a =COUNT function for counting the number of items in a range, and even a =COUNTIF function for counting specific items in a range. But how about a function to count the number of actual items or values in a list? This is a short post on how to set up a function to count unique items on a list in Excel.

There is no native function for performing this activity in Excel, so an custom array formula does the work. Unlike other regular formulas, array formulas (also known as CSE formulas) need to be entered using Ctrl + Shift + Enter. You'll know your array formula is active when you see the curly braces wrapped around your formula.

The array formula to count the unique values is below:

=SUM(1/COUNTIF(C4:C26,C4:C26))

Count Unique Values in an Excel List (Array Formula Method)

Frequently Asked Questions