Shakil Ahmed
Working with a large dataset in Excel, you may often need to know how many duplicate and unique values are there. You are definitely not going to want to count this manually and moreover, it can be time-consuming in most cases. This tutorial will show you how you can count unique values using Excel formula.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Functions You May Need to Know
1. COUNTIF
The COUNTIF function counts cells in a range that meets a particular condition.
Syntax of the COUNTIF function is as follows
COUNTIF (range, criteria)
Arguments:
| Argument | Required/Optional | Explanation |
|---|---|---|
| range | Required | Range of cells where the criteria will be assigned for counting. |
| criteria | Required | Condition or criterion for the selected range of cells. |
To know more about the COUNTIF function you can explore Microsoft Support site.
2. FREQUENCY
The FREQUENCY function calculates how often values occur within a range of values. It returns a vertical array of numbers.
Syntax of the FREQUENCY function is
FREQUENCY (data_array, bins_array)
Arguments:
| Arguments | Required/Optional | Explanation |
|---|---|---|
| data_array | Required | It is an array of values for which you want to get frequencies. |
| bins_array | Required | It is an array of intervals for grouping values. The interval is also known as “bins”. |
You can know more about the FREQUENCY function from Microsoft Support site.
3. SUMPRODUCT
The SUMPRODUCT function returns the sum of the products of corresponding ranges or arrays.
SUMPRODUCT(N number of array)
N can be any number equal or more than 1.
Arguments:
| Argument | Required or Optional | Value |
|---|---|---|
| array 1 | Required | The first array of numbers. |
| [array2] | Optional | The second array of numbers. |
| [array3] | Optional | The third array of numbers. |
To know more about the SUMPRODUCT function you can explore Microsoft Support site.
4. MATCH
The MATCH function is used to locate the position of a specified item in a range of cells. It returns the relative position of a particular item in the range.
Syntax of the MATCH function is
MATCH(lookup_value, lookup_array, match_type)
Arguments:
| Argument | Required/Optional | Explanation |
|---|---|---|
| lookup_value | Required | The value to match in the array |
| lookup_array | Required | A range of cells or an array reference where to find value |
| match_type | Optional | Specifies how Excel matches lookup_value with values in lookup_array. Here, 1 = exact or next smallest, 0 = exact match and -1 = exact or next largest |
For a deeper understanding, you can check out the Microsoft support site.
4 Simple Methods to Count Unique Values with Formula in Excel
In the following, I have described 4 simple and quick methods to count unique values with formula in Excel.
Here we have a concise data set of a few Employees with their Addresses and Salaries. For keeping things simpler we are using this basic set of data.
In the practical world, you may encounter many complex data sets and relationships.
Using the Employee Name we will show how to count unique text values and using the Salary column we will show how to do it for numbers.
1. Using Excel Advanced Sort & Filter Option
You can use the Sort & Filter option for counting unique values using formula in Excel.
Steps:
- First, select the cells or column you want to extract unique values.
- Then on the Data tab you will find the Sort & Filter Inside there, you will notice the Advanced option.
- Click Advanced. A new dialog box will pop up in front of you.
- Now select Copy to another location and select range from the table.
- Then, clicking the “Copy to” section choose a cell (F5) to copy unique values.
- Do not forget to check Unique records only.
- Finish the procedure clicking OK.
- You will get the unique values from your selected cells.
- Now you can further count them using the ROWS function to count unique values. Simply, choose a cell (F14) and apply the below formula down-
=ROWS(F4:F10)
- Gently, hit ENTER and you will get the unique values counted in the selected cell.
You can also use the COUNTA function as well, since it will count irrespective of the type of your values. Similarly, select a cell (F16 ) and write the below formula-
=COUNTA(F4:F10)
- Similarly, click ENTER and you will get the output in your hands.
2. Counting Unique Text Values Using Multiple Formula
2.1. Using Basic SUMPRODUCT and COUNTIF Functions
We will use SUMPRODUCT and COUNTIF functions to extract the number of unique presents within a range.
Steps:
- Starting with, choose a cell (F5) and apply the below formula-
=SUMPRODUCT(1/COUNTIF(B5:B16,B5:B16))
- Here our data_range and criteria_range will be the same. Because for each value in the data range, COUNTIF will check how many times that occurs in the criteria_range, so our data_range and criteria_range should be the same.
- Inside mechanism of the COUNTIF function will result in an array that contains the occurrence number for each value. Then SUMPRODUCT will sum all the values inside the array and return the result.
2.2. Combine IF, COUNTIF, and SUMPRODUCT Functions
Sometimes you might find blank cells inside a range. In that case, after applying formula, you will get to see “#Div/0!” error. Well, with the combination of IF, COUNTIF, and SUMPRODUCT functions you can count unique text values whether there is a blank cell inside the range. Follow the instructions below-
Steps:
- Simply, choose a cell (F5) and write the formula down-
SUMPRODUCT(IF(B5:B16<>"",1/COUNTIF(B5:B16, B5:B16), 0))
- Hence, press ENTER to get the output.
Read More: Count Unique Values with Criteria by SUMPRODUCT in Excel
2.3. Using Array to Count
So far we have discussed the formula which is useful for small ranges. The SUMPRODUCT-COUNTIF formula will get slower with the increase of range. To resolve this issue, we can use the MATCH and FREQUENCY functions.
Step 1:
- First, choose a cell (F5) and put the formula down-
=SUMPRODUCT(IF(FREQUENCY(MATCH(B5:B16,B5:B16,0),MATCH(B5:B16,B5:B16,0))>0,1))
- Here FREQUENCY function gives an array containing the number of occurrences for a value for the first occurrence and for the next occurrence of that value, it returns 0.
- The MATCH function is used for returning the position in the range. These two MATCH functions returned the position and then used as an argument to the FREQUENCY function which gives us a count of the total number of distinct values.
For keeping things simple and similar we are using the same set of examples, you will find this formula faster compared to the earlier one while dealing with a large data set. You can also try the combination of the ROW, MATCH, FREQUENCY, IF, SUMPRODUCT functions.
Step 2:
- Just like the previous methods, choose a cell (F5) and write the below formula inside the cell-
=SUMPRODUCT(IF(FREQUENCY(IF(B5:B16<>"",MATCH(B5:B16,B5:B16,0)),ROW(B5:B16)-ROW(B5)+1),1)) 
- Again this formula uses the FREQUENCY function to count the unique values. And we will use the MATCH and ROW functions within the FREQUENCY function to provide its two placeholders.
- The ROW function returns the row number of each cell in range. Since we subtracted the first – row number from each row, it returns an array of serial from the value 1.
- The MATCH function in this formula will give us the position number of each item that appears in the data range. It will return values as an array.
- Inside the IF statement to check for any empty cell. These arrays are fed to the FREQUENCY function which returns how often values occur within the set of data.
- The outer IF function sets each unique value to 1 and duplicate value to FALSE. And the SUMPRODUCT counts the non-zero elements within the array and gives the total.
Read More: How to Use COUNTIF for Unique Text
3. Count Unique Numbers
3.1. Using FREQUENCY Function
Counting unique numbers will be much easier using the FREQUENCY function. This function is typically for the numbers that’s why we couldn’t use it directly for the text values.
Step 1:
- Write the below formula down in the selected cell (F5)-
=SUMPRODUCT(IF(FREQUENCY(D5:D16,D5:D16),1))
- Similarly, to the COUNTIF function, FREQUENCY function returns an array of each value occurrence. But only for numbers.
- Here, the FREQUENCY function returns the occurrence number for the first one and 0 for the rest of the same value.
- IF function will interpret the non-zero elements as TRUE and 0 as FALSE. For each non-zero value it will set 1 in the array. Then with the SUMPRODUCT function it will provide the total amount.
Instead of the IF function we can write the formula using the SIGN function. This is the simplest Excel formula to count unique values.
Step 2:
- Presently, choose a cell (F5) and apply the below formula-
=SUMPRODUCT(SIGN(FREQUENCY(D5:D16,D5:D16)))
- Earlier, we have set the IF function in such a way that it will replace non-zero as 1. But the SIGN function will do it automatically. SIGN interprets all the non-zero (positive) numbers as 1 and zero as 0.
- If you can remember the array returned by the FREQUENCY function (can scroll up and see) then you will understand that for non-zero elements SIGN provided 1. Then use SUMPRODUCT to get the total output.
3.2. Using ISNUMBER Function
For counting unique numbers you can use the ISNUMBER function too. Follow the steps below-
Steps:
- Simply, choose a cell (F5) and apply the formula below-
=SUMPRODUCT(1/COUNTIF(D5:D16,D5:D16),--ISNUMBER(D5:D16))
- The COUNTIF function here, will result in an array that contains the instance number for each value.
- The ISNUMBER function here will result in an array of values TRUE for numbers and False for others. Adding two hyphens (–) before ISNUMBER will convert TRUE into 1.
4. UNIQUE Function to Count Values
If you are using EXCEL 365, then in order to count unique values, you can use the UNIQUE function as well.
Steps:
- First, select a cell (F5) and write the below formula inside the cell-
=COUNTA(UNIQUE(B5:B16))
- The UNIQUE function will fetch the non-duplicate values and the COUNTA function will count the non empty cells within range.
- So, using the UNIQUE function inside a COUNTA function leads us to the result we are looking for.
Conclusion
That’s all for today. We have tried listing out several formulas to count unique values in Excel. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. You are welcome to notify us of any other methods to the task.
