If you’re working with larger data sets, you’ll commonly encounter duplicates in your spreadsheets. This is the main reason you’ll have to count unique values in Excel.

Luckily, Excel has various capabilities to help you handle even the biggest data dumps.

As such, several methods exist to count the number of unique values in Excel. This guide will cover them all in detail with easy-to-understand instructions and screenshots.

What Are Distinct and Unique Values in Excel?

What are the unique values in Excel? These values only appear once in a data set. For example, consider the dataset: “1, 2, 2, 3.” In this data, 1 and 3 would be the unique values.

Whereas distinct values in a list are unique from each other. For example, in the data set “1, 2, 2, 3.” The distinct values here would be “1, 2, 3.”

Related: Count Unique Values in Google Sheets

Using the COUNTIF Formula to Count Unique Values in Excel

In addition to utilizing the SUM function, the IF function, and COUNTIF formulas can be used to count unique Excel values in any given range.

When using COUNTIF, a “count unique values Excel formula” would look something like this:

=SUM(IF( COUNTIF( range, range) = 1, 1, 0)) 

To include Excel count unique values in a column:

1. In your spreadsheet, determine the range that to discover unique values within. Here, we’ll count unique values within the names column.

2. Select a new cell and input the equals sign (=), followed by the SUM function, IF function, and COUNTIF function:

   =SUM(IF(COUNTIF(

3. Select the range by highlighting the cells or inputting it manually. Remember to press Shift + Ctrl + Enter on your keyboard for Excel to place the curly brackets automatically.

The formula should look like this:

{=SUM(IF(COUNTIF(B3:B13, B3:B13) = 1, 1, 0))}

4. Once you’ve entered the formula, press “Enter.” The amount of Excel unique values inside the range will be displayed. In our case, there are four unique values.

Count Unique Number Values Only

To count only numbers while ignoring text, add the ISNUMBER function in the original array formula.

The new array formula will be:

=SUM( IF( ISNUMBER( range )*COUNTIF( range, range) = 1, 1, 0))

The steps are also similar to how you use the ISTEXT function.

1. Determine the range of unique number values you’d like to count. For example, let’s say we want to count the unique number values in the values list from our sample sheet.

2. After determining the range, select a new cell and input the new array formula with the ISNUMBER function added.

In our case, the formula will be:

=SUM(IF(ISNUMBER(B3:B13),1/COUNTIF(B3:B13, B3:B13),""))

Press Shift + Ctrl + Enter after inputting the new formula in a new cell.

3. The formula will count and display the unique number values (in the specified range). In our case, the unique number value is 2.

Because Excel also considers dates and times as serial number values, they can be counted using the same formula.

How To Count Text Values Only in Excel

When the dataset contains a mixture of numeric and text, but you only wish to tally the text values in the list, then you should use ISTEXT alongside your formula.

The new syntax would be:

=SUM(IF(ISTEXT(range)*COUNTIF(range,range) = 1, 1, 0)) 

If we use the above syntax in a spreadsheet to count unique values in Excel with multiple criteria:

1. Determine the range of unique text values to count. Highlight it.
2. Select a new cell and input the new array formula with the ISTEXT function added.

In our case, the formula will be:

=SUM(IF(ISTEXT(B3:B13)*COUNTIF(B3:B13,B3:B13) = 1, 1, 0))

3. Press Shift + Ctrl + Enter after inputting the new formula in a new cell to make it an array formula.
4. The formula will count and display unique values within the specified range.

Using the Advanced Filter Tool to Count Unique Values in Excel

Another way to find the number of unique values in Excel is by using the Advanced filter tool. Unlike Google Sheets, this filter tool can extract data from a table. This is an easy and handy tool for counting unique values in Excel.

1. Highlight the range of cells containing the desired data.

2. Navigate to the “Data” tab in the Excel ribbon.

3. Once in the data tab, click the “Advanced” button in the “Sort & Filter” section.

4. After clicking Advanced, a dialogue box will appear. Select “Copy to another location.”

5. Specify the destination to copy the unrepeated values provided for you in the dialogue box.

6. Check “Unique records only.”

7. Click on “OK.” Excel will automatically copy the highlighted values and paste them into the range of cells you specified (in the Copy to field).

8. After the unrepeated values are copied, you can utilize the COUNT or COUNTA function to tally them.

For example, you might use:

=COUNTA(H2:H9) 

Place this formula in an empty cell to count the specified cells. In our sheet, the amount of unique values in our example is 8.

How To Count Distinct Values in Excel

Excel offers several tools for counting distinct values efficiently. These are the tools, techniques, and formulas to do so.

1. Using COUNTIF to Count Distinct Values in Excel

For this method, we use the SUM function and the COUNTIF function to tally the distinct values.

The formula to tally the distinct values will follow the following syntax:

=SUM( 1/ COUNTIF( range, range)) 

Just like with finding unique values, remember to use Shift + Ctrl + Enter to make an array.

Here are the steps to using the formula:

1. Determine the range in which you wish to count distinct cells from. Let’s use the spreadsheet below as an example and try to add up the number of distinct values in the names list.

2. After finding the range, insert the formula in an empty cell and Press Shift + Ctrl + Enter on your keyboard.

Our formula will look like this:

{=SUM( 1/ COUNTIF(B3:B13, B3:B13))}

3. The formula will then count the number of distinct values in the specified range of cells and display the number in the cell where the formula was inserted. Our sample came out as 7.

That is how you count the number of unique values in a column in Excel using the COUNTIF function. However, there is another alternative. Instead of using the SUM function, you can utilize the SUMPRODUCT function and still get the same results.

Here is how you use the SUMPRODUCT function together with the COUNTIF function instead of the SUM function.

1. Determine the range you want to add up distinct values only in your worksheet. Let’s use the same worksheet as the one used above as an example and try to add up the distinct values in the names list using the SUMPRODUCT function.

2. The next step is to select an empty cell to place the new array formula, which will follow this syntax:

=SUMPRODUCT( 1/ COUNTIF(range, range)

In our example, the formula will be as shown below:

{=SUMPRODUCT( 1/ COUNTIF(B3:B13, B3:B13))}

When using this method, you do not have to Press Shift + Ctrl + Enter after inputting the formula. You can press “Enter.”

3. The number of distinct values in the specified range of cells will be displayed in the cell where you implemented the formula. You can see that the amount of distinct values in our names list is 7, just like when we used the SUM function together with the COUNTIF function.

2. How to Count Distinct Number Values Only in Excel

If you want to sum up the distinct number values only in a list containing both text and number values, you have to use ISNUMBER instead of ISTEXT.

This is the syntax for the formula:

=SUM(IF(ISNUMBER(range), 1/COUNTIF(range, range), “”))

Here is how you apply it:

1. Determine the range of cells you wish to count distinct number values from
2. Select an empty cell and insert the formula. Press Shift + Ctrl + Enter after inputting the formula.

Here is what we see in the example:

=SUM(IF(ISNUMBER(B3:B13), 1/COUNTIF(B3:B13, B3:B13), ""))

3. The formula will then display the number of distinct text values.
   

   

3. How To Count Distinct Text Values Only in Excel

Your worksheet may have a mixture of text values and number values in a list, and you may want to count only the distinct text values. This is another situation you may encounter when trying to add distinct values to a dataset.

To accomplish this, we use the following formula format:

=SUM(IF(ISTEXT(range), 1/COUNTIF(range, range), “”))

I’m sure you noticed that the formula is not very different, and that is because we embedded the ISTEXT function in the formula to add distinct values.

Here is how you apply the formula:

1. Determine the range in which you wish to count distinct text values only.

2. Select an empty cell and input the formula. Press Shift + Ctrl + Enter after inputting the formula.

Here’s what we used in the example

=SUM(IF(ISTEXT(B3:B13), 1/COUNTIF(B3:B13, B3:B13), ""))

3. The formula will count only the distinct text values and show the number in the cell you placed the formula in.

Related: The Best Microsoft Excel Courses on the Web

4. How To Count Distinct Values While Ignoring Empty or Blank Cells in Excel

The list or range of cells you add distinct values to may have blank spaces. That may interfere with the methods mentioned above.

In this example, we use a similar formula to our first distinct values example. However, we also use the IF function.

To add up the distinct values while ignoring empty cells in Excel:

=SUM(IF(range<>””,1/COUNTIF(range, range), 0))

Since this is an array Excel formula, remember to press Shift + Ctrl + Enter after including the formula:

1. Determine the range to count distinct cells that have blank spaces.

2. Select an empty cell and insert the array formula containing the IF function:

{=SUM(IF(B3:B13<>"",1/COUNTIF(B3:B13, B3:B13), 0))}

3. Press Shift + Ctrl + Enter after inputting the formula (to make it an array formula).
4. The number of distinct cells will be displayed in the cell where you’ve implemented the formula.

Final Thoughts

As you can see, there are many ways to count unique values in Excel (and add distinct values, too). Generally speaking, these formula solutions are ideal as they provide results directly into your spreadsheet.

Looking to skip the learning curve and start using some powerful pre-made spreadsheets? Check out our premium template library. When you use the code “SSP,” you’ll save 50% at checkout!

Get Premium Templates

Related:

• Can Excel Count Cells with Text? [Easy Guide]
• Guide: Excel IF Statements with Multiple Conditions
• The Ultimate Google Sheets Formulas Cheat Sheet
• How To Find Duplicates in Excel [Easy Guide]
• How To Add Leading Zeros in Excel [Easy Guide]