Count the Number of Unique Values in a List in Excel

Use a formula to count the number of unique values that are contained within a list in Excel.

Steps to Count the Number of Unique Values in a List

We need to enter a rather long formula, so it will be listed here in small parts.

1. Type =SUM(
   
   
2. Type 1/
   
   
3. Type COUNTIF(
   
   
4. Select the range of data that contains the values:
   
5. Type a comma and then select that same range of values once more:
   
6. Type two closing parenthesis.
   
7. Now, instead of hitting Enter, press Ctrl + Shift + Enter.  If you do this correctly, you should see curly braces {} now surrounding your formula when you look to the formula bar.
   

This is the final formula:

The curly braces have not been included in the formula because they do not appear until you enter the formula using Ctrl + Shift + Enter and, in fact, you will need to use that way to input this formula any time you go to edit it.

Notes

The last step is what makes this formula count the number of unique values in the list.  Hitting Ctrl + Shift + Enter to input the formula makes the formula an array formula, which is what makes it all work.

There is no point to learn everything about this formula because, honestly, array formulas are a real pain in the you-know-what.  The important thing is to bookmark this tutorial and come back to it when you need to have a formula that counts the number of unique values in a list or range in Excel.

Download the sample file that accompanies this tutorial so you can see this formula in action or if you just want to copy it instead of typing it in.