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.
- 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.
- 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.
This is all about that how we can count the unique entries & thus avoiding any duplicates in a range in Microsoft Excel 2010 and 2013.