Microsoft Excel is a widely used tool, especially in the business world. It can handle various tasks, from basic data entry to complex financial analyses. One essential skill for working with data in Excel is counting unique values. Whether managing customer information, analysing survey responses, or tracking inventory, knowing how to count unique values efficiently can save you time and provide accurate insights.

In this article, we have discussed how to count unique values in Excel using filter options and formulas.

Also read: How to count cells with text in Excel?

This is a simple method to use to count unique values in Excel. You can use the Filter option to select the different values. This feature separates these values into another row. After that, you can use the ROWS function to figure out how many unique values there are. To use this method, follow the steps below.

Step 1: Open the spreadsheet, click on the Data tab, then click on the Advanced button in the Sort & Filter section.

This is an image of count unique value excel ss1

Step 2: In the filter box, select the Copy to another location; then in the List Range box you can enter the cells manually on which you want to apply the filter, or you can click in the box and then select the area from your mouse, leave the Criteria range.

In the Copy to box, select a cell where you want to copy the results, then checkmark the box beside Unique records only and click the OK button.

This is an image of count unique value excel ss2

Step 3: Unique values from B2 to B9 are copied to column D.

This is an image of count unique value excel ss3

Step 4: To count the number of unique values, you can select the column and click on Quick Analysis or press the Ctrl + Q.

This is an image of count unique value excel ss4

Step 5: Click on the Totals tab and then click on the Count option.

This is an image of count unique value excel ss6

Step 6: The result will be shown in the cell below the unique values.

This is an image of count unique value excel ss5

Alternate Step to count the number of unique values: You can also use the ROWS formula to count the rows. Click on the cell below the appeared results and enter the formula =ROWS(D2:D5), here D2 and D5 are the cells where the results of our example sheet appeared; you can replace them with the cells you have chosen for the result on your spreadsheet.

This is an image of count unique value excel ss7

Also read: How to change Outlook font size?

Count unique values using SUM, IF, and COUNTIF functions

Enter the formula =SUM(IF(COUNTIF(range,range)=1,1,0)) in the cell you want the result. The range is the starting and the ending cell in which you want to search for unique values. This is an array formula that stores counted values in a new array. Because it is an array formula, press Ctrl + Shift + Enter.

For instance, we entered the formula =SUM(IF(COUNTIF(B2:B9,B2:B9)=1,1,0)) to find the unique values in cell range B2 to B9.

This is an image of count unique value through sum if ss1

The result came out to be two.

This is an image of count unique value through sum if ss2

How does this formula work?

The COUNTIF function calculates the count of individual values within the range B2 to B9. This count signifies how many times each value appears in the range. These counted results are organised into an array, creating something like (1, 3, 3, 1). Electronics and Books occurred only once, while Clothing and Food occurred thrice.

Then, the IF function selects only the unique values equal to 1 and replaces any other values with 0. This action transforms the array to [1, 0, 0, 1]. Lastly, the SUM function adds up the unique values and provides the result of 2.

Count Unique Text values

In certain tables and worksheets, text and numbers could be mixed. When encountering such situations, you can use the above-explained function with a slight adjustment to identify unique text values within the spreadsheet.

Enter the formula =SUM(IF(ISTEXT(range)*COUNTIF(range,range)=1,1,0)) and press Ctrl + Shift + Enter. Building upon the regular formula, we have included an ISTEXT element to detect unique text values. When a value is a text, the ISTEXT function gives a result of 1, causing the value to be counted in an array. If the cell contains something other than text, the result is 0.

For instance, we have entered the formula =SUM(IF(ISTEXT(B2:B9)*COUNTIF(B2:B9,B2:B9)=1,1,0)) to find the unique values in cell range B2 to B9.

This is an image of count unique text value ss1

The result is two.

This is an image of count unique text value ss2

Count Unique Numeric values

If you specifically want to count unique numeric values mixed with text, you can use this formula =SUM(IF(ISNUMBER(range)*COUNTIF(range,range)=1,1,0)) and press Ctrl + Shift + Enter. In this case, the ISNUMBER function gives a result of 1 for numeric values and ignores other types of values. This function operates much like what was explained in the ISTEXT element.

For instance, we have entered =SUM(IF(ISNUMBER(B2:B9)*COUNTIF(B2:B9,B2:B9)=1,1,0)) to find the unique values in cell range B2 to B9.