How to Count Unique Names in Excel (5 Methods)

SUMPRODUCT with FREQUENCY Function to Count Unique Names

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.

Download Practice Workbook

Download this practice sheet to practice while you are reading this article.

Count Unique Names in Excel (5 Methods)

1. Using SUMPRODUCT Function to Count Unique Names

The simplest and easiest way to count unique names in Excel is using the SUMPRODUCT function. Using this function we can count unique values in two ways. Let’s learn these ways.

i. SUMPRODUCT with COUNTIF

Step-1:

In the following situation, we are given a dataset where the names of some sales reps and their salary are given in the “Sales Rep” and “Salary” columns. Now we have sales reps whose names appeared more than once. So we have to count the unique number of the sales rep’s name in cell E4 under the heading “Count Unique Names”.

SUMPRODUCT with COUNTIF Function to Count Unique Names

Step-2:

Now in cell E4, 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(B4:B17,B4:B17))

Where,

  • Range and Criteria are B4:B17
  • The COUNTIF function looks into the data range and count the number of times each names appear in data range {3,2,1,2,3,3,4,3,3,1,1,1,1,1}
  • 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.

SUMPRODUCT with COUNTIF Function to Count Unique Names

Press Enter to get the unique values.

SUMPRODUCT with COUNTIF Function to Count Unique Names

Step-3:

There is a flaw in this function that if there is a Blank Cell in the data set, then the formula will fail. Because COUNTIF function generates “0” for each blank cell and 1 divided by 0 returns a divide by zero error (#DIV/0!)

SUMPRODUCT with COUNTIF Function to Count Unique Names

Step-4:

To overcome this situation let’s modify the formula a little bit. Now our new formula for this situation is,

=SUMPRODUCT(((B4:B17<>””)/COUNTIF(B4:B17,B4:B17&””)))

Now if there is any blank cell in the dataset, the formula will ignore it.

SUMPRODUCT with COUNTIF Function to Count Unique Names

Press Enter to get the result.

SUMPRODUCT with COUNTIF Function to Count Unique Names

ii. SUMPRODUCT with FREQUENCY

Step-1:

We will use the same data range that we used in the previous example.

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(B4:B17,B4:B17,0),ROW(B4:B17)-ROW(B4)+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 B4:B17,B4:B17,0.
  • The bins_array argument is constructed from this part of the formula (ROW(B4:B17)-ROW(B4)+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.

SUMPRODUCT with FREQUENCY Function to Count Unique Names

Since this is an Array Formula, press “CTRL+SHIFT+ENTER” to apply the formula. And we have got our final count.

SUMPRODUCT with FREQUENCY Function to Count Unique Names

Read more: Count Unique Values with Criteria by SUMPRODUCT in Excel

2. Using SUM with COUNTIF Formula to Count Unique Names

Step-1:

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(B4:B17),1/COUNTIF(B4:B17,B4:B17),””))

Where,

  • The ISTEXT function returns TRUE for all the values that are text and false for other values.
  • Range and Criteria are B4:B17
  • 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,3,3,4,3,3,1,1,1,1,1}
  • The SUM function computes the sum of all the values and returns the result.

Using SUM with COUNTIF Formula to Count Unique Names

Step-2:

Since this is an Array Formula, press “CTRL+SHIFT+ENTER” to apply the formula. And we have got our final count.

Using SUM with COUNTIF Formula to Count Unique Names

Read More: Excel Formula Count Unique Values (3 Easy Ways)

3. Using SUM with FREQUENCY and MATCH Formula to Count Unique Names

Step-1:

Now we will use the SUM with FREQUENCY and MATCH formula 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))

The final formula after the value insertion is,

=SUM(IF(FREQUENCY(IF(B4:B17<>””,MATCH(B4:B17,B4:B17,0)),ROW(B4:B17)-ROW(B4)+1),1))

Where,

  • Here in the MATCH function the lookup_value, lookup_array and [match type] is B4:B17,B4:B17,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 B4:B17<>””
  • The bins_array argument is constructed from this part of the formula (ROW(B4:B17)-ROW(B4)+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

Using SUM with FREQUENCY and MATCH Formula to Count Unique Names

Press “CTRL+SHIFT+ENTER” to apply the array formula.

Using SUM with FREQUENCY and MATCH Formula to Count Unique Names

Read More: Count Unique Text Values with Criteria in Excel (5 Methods)


Similar Readings


4. Using the UNIQUE Function to Count Unique Names

Step-1:

The UNIQUE function is available for only the Excel 365 version.

Now apply the UNIQUE function. The generic formula is,

=COUNTA(UNIQUE(range))

After inputting the values, the final form is,

=COUNTA(UNIQUE(B4:B17))

Using the UNIQUE Function to Count Unique Names

Press Enter to get the result.

Using the UNIQUE Function to Count Unique Names

Step-2:

You can also get the list of unique names by using this UNIQUE function. For this, the formula is,

=UNIQUE(B4:B17)

Press Enter to continue.

Using the UNIQUE Function to Count Unique Names

 

Read More: Excel VBA: Count Unique Values in a Column (3 Methods)

5. Using Advanced Filter to Count Unique Names in Excel

Step-1:

We can also use the Advanced Filter option to count unique names. To do that, go to Data, in the Sort & Filter group, click on Advanced.

Using Advanced Filter to Count Unique Names in Excel

Step-2:

Advanced Filter window appears. Here check on Copy to Another Location and Use Unique Records Only.

Using Advanced Filter to Count Unique Names in Excel

Step-3:

Now choose the data source for the List Range ($B$3:$B$17), Criteria Range ($B$3:$B$17), and Copy to $E$3. Click Ok to continue.

Using Advanced Filter to Count Unique Names in Excel

And our list of the unique names is made.

Using Advanced Filter to Count Unique Names in Excel

Step-4:

To count the unique names, just use this formula,

=ROWS(E4:E9)

And press Enter.

Using Advanced Filter to Count Unique Names in Excel

Read More: Count Unique Values with Criteria by COUNTIFS in EXCEL (4 Examples)

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.

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.


Related Articles

Asikul Himel
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo