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))