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?
Count unique values using the filter option
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.
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.
Step 3: Unique values from B2 to B9 are copied to column D.
Step 4: To count the number of unique values, you can select the column and click on Quick Analysis or press the Ctrl + Q.
Step 5: Click on the Totals tab and then click on the Count option.
Step 6: The result will be shown in the cell below the unique values.
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.
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.
The result came out to be two.
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.
The result is two.
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.
The result is one.
Also read: How to add a total row in Excel?
