Count the Number of Unique Values in a List in Excel

Add to Favorites

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:

=SUM(1/COUNTIF(A1:A7,A1:A7))

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.


Excel Function: COUNTIF(), SUM()
Downloadable Files: Excel File

Similar Content on TeachExcel
Highlight and Sort the Top and Bottom Performers in a List in Excel
Tutorial: How to highlight the rows of the top and bottom performers in a list of data. This allow...
Get the Name of a Worksheet in Macros VBA in Excel
Tutorial: How to get the name of a worksheet in Excel using VBA and Macros and also how to store tha...
Count The Number of Words in a Cell or Range of Cells in Excel - UDF
Macro: Count words in cells with this user defined function (UDF). This UDF allows you to count t...
Count the Number of Cells that Start or End with Specific Text in Excel
Tutorial: How to count cells that match text at the start or the end of a string in Excel. If you w...
Show All Formulas in a Worksheet in Excel
Tutorial: Display all formulas instead of their output values. This allows you to quickly troubles...
Loop through a Range of Cells in a UDF in Excel
Tutorial: How to loop through a range of cells in a UDF, User Defined Function, in Excel. This is ...