Sometimes we may want to know the number of unique values in a dataset.
For example, if you have a list of names with repetition, you may want to find out the count of the unique names in the list.
This tutorial explains 5 methods of how to count unique values in Excel using formulas.
Method #1: Count Unique Values Using COUNTA and UNIQUE Functions
Suppose we have the following dataset of payment options preferred by selected customers.
We use the combination of COUNTA and UNIQUE functions to return the number of unique payment options represented in the dataset.
We use the steps below:
- Select cell D2 and type in the formula below:
=COUNTA(UNIQUE(B2:B15))
- Press Enter on the keyboard or click the Enter button on the Formula Bar.
The formula returns the value 10 as the number of unique payment options preferred by the selected customers.
Explanation of the formula
=COUNTA(UNIQUE(B2:B15))
- The UNIQUE function extracts and returns 10 unique payment options in the range B2:B15.
- The COUNTA function returns the value of 10 which is the number of unique payment options returned by the UNIQUE function.
This formula is dynamic and recalculates when there is a change in the target array.
One drawback of this formula is that it includes empty cells in the count of unique values. There are 10 unique payment options in our dataset but the count increases to 11 when there is an empty cell in the dataset:
To prevent the inclusion of empty cells in the count of unique values, use the following method.
Method #2: Count Unique Values Using UNIQUE, COUNTA, and FILTER Functions
Suppose we have the following dataset of payment options preferred by selected customers. The dataset has an empty cell B3.
We use a combination of UNIQUE, COUNTA, and FILTER functions to return the count of unique payment options represented in the dataset while excluding the empty cell B3.
We use the following steps:
- Select cell D2 and type in the following formula:
=COUNTA(UNIQUE(FILTER(B2:B15,B2:B15<>"")))
- Press Enter on the keyboard or click the Enter button on the Formula Bar.
The formula returns 10 as the number of unique payment options preferred by the selected customers. The empty cell B3 is excluded from the count.
Explanation of the formula
- FILTER(B2:B15,B2:B15<>””). The FILTER function filters the empty cell from the data range as shown below:
- UNIQUE(FILTER(B2:B15,B2:B15<>””)). The UNIQUE function extracts unique values from the filtered range as shown below:
- =COUNTA(UNIQUE(FILTER(B2:B15,B2:B15<>””))). The COUNTA function counts the unique values returned by the UNIQUE function and returns the value 10.
Method #3: Count Unique Values Using SUMPRODUCT & COUNTIF Functions
Suppose we have the following dataset of payment options preferred by selected customers.
We use the combination of SUMPRODUCT and COUNTIF functions to return the number of unique payment options represented in the dataset.
We proceed as follows in the application of the functions:
- Select cell D2 and type in the formula below:
=SUMPRODUCT(1/COUNTIF(B2:B15,B2:B15))
Note: The data range and the criteria range are the same. This is because, for each value in the data range, the COUNTIF function checks how many times it appears in the criteria range.
- Press Enter on the keyboard or click the Enter button on the Formula Bar.
The formula returns 10 as the number of unique payment options preferred by the selected customers.
Explanation of the formula
=SUMPRODUCT(1/COUNTIF(B2:B15,B2:B15))
- COUNTIF(B2:B15,B2:B15) This part of the formula returns the array {1;2;1;2;2;1;2;1;1;1;2;2;2;2}. These numbers show how many times a value appears in the given range. In this case, Amazon Pay occurs once, PayPal appears twice, and so on.
- 1/COUNTIF(B2:B15,B2:B15) In this part of the formula each element of the array returned by the COUNTIF function is divided by 1. This results in the array {1;0.5;1;0.5;0.5;1;0.5;1;1;1;0.5;0.5;0.5;0.5}.
- SUMPRODUCT(1/COUNTIF(B2:B15,B2:B15)) The SUMPRODUCT function adds all the numbers in the array and returns the value of 10 as the number of unique payment options preferred by the selected customers.
Note: Each payment option that appears once in the range is represented by the value of 1. For example, Amazon Pay occurs once in the range and is represented by the value of 1.
Each occurrence of a payment option that occurs more than once is represented by a fractional value. For example, PayPal appears twice in the range and each of the instances is represented by a value of 0.5. When we add the fractional values for each payment option we get a value of 1.
When all the values in the array are added we get the value of 10 which is the total count of all the unique payment options in the range.
One drawback of this formula is that if it encounters a blank cell in the data range it returns a #DIV/0! error:
The error is explained as follows:
- COUNTIF(B2:B15,B2:B15) This part of the formula returns the array {1;0;1;2;2;1;2;1;1;1;2;2;2;2}. Notice the 0 value, the second element of the array.
- 1/COUNTIF(B2:B15,B2:B15) It is in this part of the formula that the #DIV/0! error occurs. The formula attempts to divide 1 by the 0 value which is the second element of the array. The calculation fails because division by 0 (zero) is indeterminate.
To prevent the #DIV/0 error, we use the following method.
Method #4: Count Unique Values Using SUMPRODUCT, IF, and COUNTIF Functions
Suppose we have the following dataset of payment options preferred by selected customers. The dataset has an empty cell B3.
We use the combination of SUMPRODUCT, IF, and COUNTIF functions to return the number of unique payment options represented in the dataset and ignore the blank cell B3.
We proceed as follows in applying the functions:
- Select cell D2 and type in the formula below:
=SUMPRODUCT(IF(B2:B15<>"",1/COUNTIF(B2:B15,B2:B15),0))
- Press Enter on the keyboard or click the Enter button on the Formula Bar.
The formula returns 10 as the number of unique payment options preferred by the selected customers. The empty cell B3 is excluded from the count.
The IF function checks whether there is an empty cell in the range. The empty cell B3 is excluded from the range passed to the COUNTIF function. The empty cell B3 is replaced with a 0 (zero) in the resultant array that is passed to the SUMPRODUCT function.
The formulas involving the COUNTIF function work well with small datasets. They however tend to slow down in the case of large datasets. For better performance when you are working with large datasets, use the following method.
Method #5: Count Unique Values Using SUMPRODUCT, IF, FREQUENCY, & XMATCH Functions
Suppose we have the following dataset of payment options preferred by selected customers.
We use the combination of SUMPRODUCT, IF, FREQUENCY, and XMATCH functions to return the number of unique payment options represented in the dataset.
We proceed as follows in applying the functions:
- Select cell D2 and type in the following formula:
=SUMPRODUCT(IF(FREQUENCY(XMATCH(B2:B15,B2:B15,0),XMATCH(B2:B15,B2:B15,0))>0,1))
- Press Enter on the keyboard or click the Enter button on the Formula Bar.
The formula returns the value 10 as the number of unique payment options preferred by the selected customers.
Explanation of the formula
=SUMPRODUCT(IF(FREQUENCY(XMATCH(B2:B15,B2:B15,0),XMATCH(B2:B15,B2:B15,0))>0,1))
Note: For better understanding of this formula, we first explain the syntax and arguments of the FREQUENCY and XMATCH functions.
The FREQUENCY function calculates how often values occur within a range of values and returns a vertical array of numbers having one more element than Bins_array.
The syntax of the FREQUENCY function:
FREQUENCY(data_array,bins_array)
The FREQUENCY function has the following arguments:
- data_array This is a required argument. It is a reference to or an array of a set of values for which to count frequencies. If data_array has no values, the FREQUENCY function returns an array of zeros.
- bins_array This is a required argument. It is a reference to or an array of a set of values in which the values in data_array are to be grouped. If bins_array has no values, the FREQUENCY function returns the number of elements in data_array.
The XMATCH function returns the comparative position of an element in an array. An exact match is required by default.
The syntax of the XMATCH function:
XMATCH(lookup_value,lookup_array,[match_mode],[search_mode])
The XMATCH function has the following arguments:
- lookup_value This is a required argument. It is the value to be searched.
- lookup_array This is a required argument. It is the range or array to search.
- [match_mode] This is an optional argument. It indicates the match type. The value 0 is the default and indicates an exact match. -1 indicates an exact match or the next smallest item. 1 indicates an exact match or the next largest item. 2 indicates a wildcard match that uses *, ?, or ~.
- [search_mode] This is an optional argument. It indicates the search type. The value 1 is the default and indicates the first-to-last search.-1 indicates a reverse search (last-to-first). 2 indicates a binary search. The look_up array must be sorted in ascending order otherwise invalid results are returned. -2 indicates a binary search. The look_up array must be sorted in descending order otherwise invalid results are returned.
We now proceed to explain the formula as follows:
- FREQUENCY(XMATCH(B2:B15,B2:B15,0),XMATCH(B2:B15,B2:B15,0)). In this part of the formula the FREQUENCY function returns the array {1;2;1;2;2;1;2;1;1;1;0;0;0;0;0}. The array has one more element than the bins_array.
The non-zero elements in the array represent the number of occurrences of the payment options in the dataset.
This number is attached to the first appearance of the option in the dataset. The zero elements are attached to the next appearance of the payment options.
For example, PayPal occurs twice in the dataset the value 2 is attached to its first appearance and the value 0 is attached to its next occurrence.
- IF(FREQUENCY(XMATCH(B2:B15,B2:B15,0),XMATCH(B2:B15,B2:B15,0))>0,1). In this part of the formula, the array returned by the FREQUENCY function is passed to the IF Function. The IF function checks that each element in the array is greater than 0 and returns the array {1;1;1;1;1;1;1;1;1;1;FALSE;FALSE;FALSE;FALSE;FALSE} as shown below:
- =SUMPRODUCT(IF(FREQUENCY(XMATCH(B2:B15,B2:B15,0),XMATCH(B2:B15,B2:B15,0))>0,1). The SUMPRODUCT function adds all the numbers in the array returned by the IF function and returns the value 10 as the number of unique payment options preferred by the selected customers. Note that in mathematical calculation Excel equates the Boolean value FALSE to 0 (zero).
In this tutorial, I showed you five different methods to count unique values in Excel. Some methods work well in all situations, while some methods work only when you have non-empty cells in the dataset.
I hope you found this tutorial useful.
Other articles you may also like: