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.

img1

  • 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.

img2

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.

img3

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.

img4

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube