In Excel the UNIQUE function returns a list of unique values from a range. This function works with any data type: text, numbers, dates, times, etc. Counting unique values is important because it enables users identify the distinct items in the data set they are working on, and get a better understanding of the true size of the data. Basically, counting unique values helps you identify inconsistencies in your data.
You can easily do this in Excel, in this article we will highlight steps on how to count unique values in Excel.
How to count unique values in Excel
To count unique values in Excel, you can use the COUNTIF function, use a combined function, or use the filter option. Let’s discuss these methods briefly.
Method #1: Use the COUNTIF function
To count the number of unique values in Excel, there is a built-in function to help users do that easily in Excel. The COUNTIF combined with the SUMPRODUCT is the built-in function. The COUNTIF function counts how many times an individual value appears in a specified range. While the IF function evaluates each value in the array returned by COUNTIF. The IF function keeps all 1’s (unique values), and replaces other values with zeros.
Then the SUM function adds up the values in the array returned by the IF function and outputs the total number of unique values to give you exact results.
Here’s how to do it:
- Select the cell the count of unique values will be displayed.
- Then enter the formula; =SUMPRODUCT(1/COUNTIF(range,range)).
- The place tagged range should have the data you want to count its unique values. It should look like this =SUMPRODUCT(1/COUNTIF(A2:A10, A2:A10))
- Press Enter. The unique values will appear.
This formula works by dividing 1 by the count of the unique values, and the result will be added up to give the count of unique values in the selected range.
Method #2: Count the number of unique values using a filter
Another alternative is to use the Advanced Filter dialog box to extract unique values from a column of data, then paste them in a new location. ROWS function will then be used to count the number of items in the new range.
- Select the range of cells, or put the active cell in a table.
- The range of cells should have a column heading.
- Go to the Sort & Filter group on the Data tab, and click Advanced.
- The Advanced Filter dialog box will appear here.
- Then click Copy to another location.
- Enter a cell reference in the Copy box .
Alternatively;
- Click on Collapse Dialog Button image. This temporarily hides the dialog box.
- Select a cell on the Excel worksheet, press the Expand Dialog Button image.
- Select Unique records only check box.
- Click OK.
- The unique values from this selected range will be copied to a new location beginning with the cell you specified in the Copy to box.
- Go to the blank cell below the last cell in the selected range.
- Enter the ROWS function.
- Use the range of unique values that you just copied as the argument, removing the column heading. It should be like this: if the range of unique values is A2:A10, enter =ROWS(A2:A10).
Method #3: Count the number of unique values using functions
You can also use a combination of the IF, SUM, FREQUENCY, MATCH, and LEN functions to count unique values in Excel, each function has its task thus:
- Use the IF function to assign a value of 1 to each true condition.
- Use the SUM function to add the total.
- Use the FREQUENCY function to count number of unique values. The FREQUENCY function will ignore text and zero values. The FREQUENCY function returns a number equal to the number of occurrences of that value if it is a first occurrence of a specific value. Then each occurrence of that same value after the first returns a zero.
- Use the MATCH function to return the position of a text value in a range. The returned value becomes an argument to the FREQUENCY function to be able to evaluate the corresponding text values.
- Then use the LEN function to find blank cells.
The blank cells have 0 length. The methods highlighted above can help you count unique values in Excel and know how many duplicate and unique values you have.
If you liked this tutorial, then kindly subscribe to our YouTube Channel for more Microsoft Excel video tutorials.
