While working with large datasets we often may need to count unique and distinct values in Excel. Excel does not have any built-in function to count unique values or text. But, there are many techniques and approaches by which we can count these distinct values. Today in this article, we will demonstrate some methods to count unique names in Excel.
Overview
In the following image, you will get an overview of the whole article.
1. Using SUMPRODUCT Function to Count Unique Names in Excel
The simplest and easiest way to count unique names in Excel is by using the SUMPRODUCT function. Using this function we can count unique values in two ways. Let’s learn these ways.
1.1. Combining SUMPRODUCT with COUNTIF
In the following situation, we are given a dataset where some Employees’ names and their Gross Salary are given. Now we have Employees whose names appeared more than once. So we have to count the unique number of Employee Names in cell H5 under the heading “Count of Unique Names”.
Step 1:
- First, in cell H5, apply the SUMPRODUCT function with the COUNTIF function.
The generic formula is,
=SUMPRODUCT(1/COUNTIF(range,criteria))- Insert the values into the function and the final form of the formula is,
=SUMPRODUCT(1/COUNTIF(B5:B15,B5:B15))Where,
- The range and Criteria are B5:B15.
- The COUNTIF function looks into the data range and count the number of times each name appear in the data range {3;2;1;2;1;2;1;1;3;3;2}
- After that, the result of the COUNTIF function is used as an advisor with 1 as the numerator. For this, numbers that have appeared only once in the array will become 1 and multiple appeared numbers will provide fractions as results.
- Finally, the SUMPRODUCT function will count those 1 and will give the result.
- Press ENTER to get the unique values.
There is a flaw in this function that if there is a Blank Cell in the data set, then the formula will fail. Because the COUNTIF function generates “0” for each blank cell and 1 divided by 0 returns a divide by zero error (#DIV/0!).
Step 2:
- To overcome this situation let’s modify the formula a little bit. Now our new formula for this situation is,
=SUMPRODUCT(((B5:B15<>"")/COUNTIF(B5:B15,B5:B15&""))) 
Now if there is any blank cell in the dataset, the formula will ignore it.
- Press ENTER to get the result.
1.2. Joining SUMPRODUCT with FREQUENCY
We will use the same data range that we used in the previous example.
Steps:
- Now apply the SUMPRODUCT with FREQUENCY function to get the unique names.
The generic formula is as follows,
=SUMPRODUCT(--(FREQUENCY(MATCH(Lookup_value,Lookup_array,[match_type])),ROW(reference)-ROW(reference.firstcell)+1),1))- Insert the values to get the final form-
=SUMPRODUCT(--(FREQUENCY(MATCH(B5:B15,B5:B15,0),ROW(B5:B15)-ROW(B5)+1)>0))Where,
- The MATCH function is used to get the position of each name that appears in the data. Here in the MATCH function the lookup_value, lookup_array and [match type] is B5:B15,B5:B15,0.
- The bins_array argument is constructed from this part of the formula (ROW(B5:B15)-ROW(B5)+1)
- The FREQUENCY function returns an array of numbers which indicates a count for each number in the array of data, organized by bin. A key feature in the operation of the FREQUENCY formula is that when a number has already been counted, FREQUENCY will return zero.
- Now, we check for values that are greater than zero (>0), which converts the numbers to TRUE or FALSE, then we use a double-negative (– –) to convert the TRUE and FALSE values to 1s and 0s.
- Finally, the SUMPRODUCT function simply adds the numbers up and returns the total.
- Since this is an Array Formula, press “CTRL+SHIFT+ENTER” to apply the formula and we have got our final count.
2. Inserting SUMPRODUCT Under AND Criteria to Count Unique Names with Criteria
If you want you can also utilize the SUMPRODUCT function under AND criteria to count unique names with criteria.
Suppose we have criteria given in cell (H5) which is “M”. Now we will generate unique names regarding the criteria.
Steps:
- First, choose a cell (H8) and write the below formula down-
=SUMPRODUCT((C5:C15=H5)*(1/COUNTIF(B5:B15,B5:B15)))
- Next, hit the ENTER key to get the count of unique names. Simple isn’t it?
To gather more ideas about the applied formula check out the first method.
3. Using SUM with COUNTIF Formula to Count Unique Names in Excel
Here we have come out with another solution to count unique names. Follow the instructions below-
Steps:
- Now we will use the SUM with COUNTIF formula to get the required count.
The generic formula for this formula is,
=SUM(IF(ISTEXT(Value),1/COUNTIF(range, criteria), ""))- Insert the values to get the final form of the formula-
=SUM(IF(ISTEXT(B5:B15),1/COUNTIF(B5:B15,B5:B15),""))Where,
- The ISTEXT function returns TRUE for all the values that are text and false for other values.
- Range and Criteria are B5:B15
- If the values is a text value, the COUNTIF function looks into the data range and count the number of times each names appear in data range {3;2;1;2;1;2;1;1;3;3;2}
- The SUM function computes the sum of all the values and returns the result.
- Since this is an Array Formula, press “CTRL+SHIFT+ENTER” to apply the formula and we have got our final count.
4. Combining SUM with FREQUENCY and MATCH to Count Unique Names
In some situations, you can also use combine the SUM, FREQUENCY, and MATCH functions to count unique names.
The generic formula is,
=SUM(IF(FREQUENCY(IF(logical test<>"", MATCH(Lookup_value,Lookup_array,[match type])),ROW(reference)-ROW(reference.firstcell)+1),1))Steps:
- The final formula after the value insertion is,
=SUM(IF(FREQUENCY(IF(B5:B15<>"",MATCH(B5:B15,B5:B15,0)),ROW(B5:B15)-ROW(B5)+1),1))Where,
- Here in the MATCH function the lookup_value, lookup_array and [match type] is B5:B15,B5:B15,0
- After the MATCH function, there is an IF The reason the IF function is needed is that MATCH will return a #N/A error for empty cells. So, we are excluding the empty cells with B5:B15<>””
- The bins_array argument is constructed from this part of the formula (ROW(B5:B15)-ROW(B5)+1)
- This resulting array is fed to the FREQUENCY function which returns an array of numbers that indicate a count for each number in the array of data
- Finally the outer IF function indicates each unique value to 1 and duplicate value to 0.
- Press “CTRL+SHIFT+ENTER” to apply the array formula.
5. Using UNIQUE Function to Count Unique Names in Excel
The UNIQUE function is available for only the Excel 365 version.
Step 1:
- Now apply the UNIQUE function. The generic formula is,
=COUNTA(UNIQUE(range))- After inputting the values, the final form is,
=COUNTA(UNIQUE(B5:B15))
- Press ENTER to get the result.
Step 2:
- You can also get the list of unique names by using this UNIQUE For this, the formula is,
=UNIQUE(B5:B15)
- Press ENTER to continue.
6. Applying Advanced Filter to Count Unique Names in Excel
We can also use the Advanced Filter option to count unique names. To do that-
Steps:
- Go to Data, in the Sort & Filter group, and click on Advanced.
- Advanced Filter window appears.
- Here check on Copy to Another Location and Use Unique Records Only.
- Now choose the data source for the List Range ($B$5:$B$15), Criteria Range ($B$5:$B$15), and Copy to $H$5. Click OK to continue.
- And our list of unique names is made.
- To count the unique names, just use this formula and press ENTER key-
=ROWS(H5:H11)
Quick Notes
➤ If there is a blank cell in the dataset when you are using SUMPRODUCT with COUNTIF formula, the result will show divide by zero error (#DIV/0!)
➤ For the Array Formula, you have to Press “CTRL+SHIFT+ENTER” simultaneously to get the result.
➤ The UNIQUE function is only available for Excel 365. Users of older versions of Excel won’t be able to use the function.
Download Practice Workbook
Download this practice sheet to practice while you are reading this article.
Conclusion
Today we learned some procedures to count unique names from a dataset. If you have any confusion or suggestions, you are most welcome to share your thoughts in the comment section.
<< Go Back to Count | Unique Values | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
