Counting number of unique entries in a specified range in Microsoft Excel

In this article, we will learn how to count the unique entries in a specified range. We use the “SUMPRODUCT” function along with “COUNTIF” function in Microsoft Excel to get the unique values.

SUMPRODUCT: This function is used for adding all the numbers in a range of cells.

The syntax of SUMPRODUCT formula:- =SUMPRODUCT(array1, [array2],…..)

Let’s understand with a simple exercise how we can use the SUMPRODUCT function with multiple criteria.

We have table in Range “A2 to C16” which contains the Agents’ score data. Column A contains date, Column B Agents’ Name and Column C contains score. Now we need to calculate the total score of Agent 4 in cell C18. Let’s see how we can use the SUMPRODUCT function to get this value.

Select the cell C18, and write the formula in the cell.
=SUMPRODUCT((B2:B16=B18)*(C2:C16))
Press Enter on the keyboard.
The function will return the score value of Agent 4.

COUNTIF:- This function is used for counting the duplicity of text or numbers in the range.

The Syntax of COUNTIF:- =COUNTIF(Range,Criteria)

To count the cells which are duplicate in a range, follow below given steps:-

Select the cell E2 and write the formula.
=COUNTIF($B$2:$B$13,B2)
Press Enter.
The function will return 2, which means Aaron is repeating 2 times in Student’s Name column.
Copy the same formula by pressing the key Ctrl+C and paste in the range E3:E13 by pressing the key Ctrl+V.

Let’s take an example to understand how we can count the unique number in a range in Microsoft Excel.

We have data in Range A2:C11 where Column A contains Agent list, column B contains city and column C contains sales quantity.

In this data, if we want to count the number of unique entries in a specified range, then we need to follow below given steps:-

Select the cell D2.
Enter the formula in cell =SUMPRODUCT((1/COUNTIF(A1:C11,A1:C11&"")))
Press Enter on your keyboard.
The function will return 28, which means 28 entries are unique out of 33 entries.