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.
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:-
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:-
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.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.