COUNT UNIQUE DUPLICATE VALUES IN EXCEL

COUNTIF is an excellent function to count only those cells whose value meets certain criteria.

But as excellent as it is, it becomes a challenge to count Unique or Repeats in a range that contain duplicates.

For example, Using the below data count;

  1. Total number of unique customers
  2. Total Non-Repeat Customers
  3. Total Repeat customers

Countif duplicates

There are 4 ways of counting unique values in excel;

  • Using an array formula (SUM & COUNTIF)
{=SUM(1/COUNTIF(C2:C23,C2:C23))} =16
  • Using SUMPRODUCT (SUMPRODUCT & COUNTIF)
=SUMPRODUCT((1/COUNTIF(C2:C23,C2:C23)))=16
  • Using a combination of SUM, IF, FREQUENCY & MATCH
=SUM(
  IF(
    FREQUENCY(MATCH(C2:C23,C2:C23,0),MATCH(C2:C23,C2:C23,0))>0
      ,1,0)
)=16

The Fourth way comes from below Comment by Hervé Thiriez ( https://monsieur-excel.blogspot.co.ke/)

  • Using a combination of SUMPRODUCT, ROW & MATCH
=SUMPRODUCT(1*(ROW(C2:C23)=MATCH(C2:C23,C:C,0)))=16 
OR {=SUM(1*(ROW(C2:C23)=MATCH(C2:C23,C:C,0)))}=16

How it works;

  • COUNTIF(C2: C23, C2: C23)generates an array of occurrences of each customer
    {2;2;1;2;2;2;2;1;1;1;1;1;1;2;2;1;1;2;2;2;2;1}
  • 1/COUNTIF(C2: C23, C2: C23) generates an array of reciprocal of occurrences
     {0.5;0.5;1;0.5;0.5;0.5;0.5;1;1;1;1;1;1;
    0.5;0.5;1;1;0.5;0.5;0.5;0.5;1}

Then Either SUM (array formula) or SUMPRODUCT adds up the reciprocals

►As for the combination of SUM, IF, FREQUENCY & MATCH;

  • The MATCH function searches for a specified item in a range of cells and then returns the relative position of that item in the range.

MATCH(C2:C23,C2:C23,0)={1;1;3;4;4;6;6;8;9;10;11;12;13;14;14;16;17;18;18;20;20;22}

  • The FREQUENCY function calculates how often values occur within a range of values, and then returns a vertical array of numbers.
FREQUENCY(MATCH(C2:C23,C2:C23,0),MATCH(C2:C23,C2:C23,0))
= {2;0;1;2;0;2;0;1;1;1;1;1;1;2;0;1;1;2;0;2;0;1;0}
  • The IF function returns a value of 1 if  Frequency is greater than Zero.
IF(FREQUENCY(MATCH(C2:C23,C2:C23,0),MATCH(C2:C23,C2:C23,0))>0,1,0)
={1;0;1;1;0;1;0;1;1;1;1;1;1;1;0;1;1;1;0;1;0;1;0}
  • The SUM function adds all the numbers returned by IF function.
    =SUM({1;0;1;1;0;1;0;1;1;1;1;1;1;1;0;1;1;1;0;1;0;1;0})=16

►Combination of SUMPRODUCT, ROW & MATCH

  • ROW(C2:C23) returns the row numbers
{2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23}
  • MATCH(C2:C23,C:C,0) just like in FREQUENCY formula above returns the relative position of each item in the range
{2;2;4;5;5;7;7;9;10;11;12;13;14;15;15;17;18;19;19;21;21;23}
  • (ROW(C2:C23)=MATCH(C2:C23,C:C,0)) returns a Boolean array TRUE= unique rows, FALSE= Duplicate rows
{TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE;
TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE}
  • 1*(ROW(C2:C23)=MATCH(C2:C23,C:C,0)) by multiplying this boolean array with 1 you convert TRUE=1 While FALSE=0
{1;0;1;1;0;1;0;1;1;1;1;1;1;1;0;1;1;1;0;1;0;1}

SUMPRODUCT just sums up the array.

 TOTAL NUMBER OF NON-REPEAT CUSTOMERS

Armed with above understanding it is easier to calculate the total number of non-repreat customers. Just sum customers whose frequency is  equal to 1

=SUM(
  IF(
    FREQUENCY(MATCH(C2:C23,C2:C23,0),MATCH(C2:C23,C2:C23,0))=1,1,0)
)=10

OR

=SUMPRODUCT(--(COUNTIF(C2:C23,C2:C23)=1)) =10

 TOTAL NUMBER OF REPEAT CUSTOMERS

As for repeat customers, just sum the customer whose frequency is more than 1

=SUM(
   IF(
   FREQUENCY(MATCH(C2:C23,C2:C23,0),MATCH(C2:C23,C2:C23,0))>1,1,0)
)=6

OR

=SUMPRODUCT(--(FREQUENCY(MATCH(C2:C23,C2:C23,0),MATCH(C2:C23,C2:C23,0))>1))=6

OR

{=SUM(IF((ROW(C2:C23)=MATCH(C2:C23,C:C,0)),0,1))=6}

DOWNLOAD WORKSHEET

RECOMMENDATION:

Watch these Videos on FREQUENCY

Print Friendly, PDF & Email

Do you want to excel in Excel?

Or

Seeking Microsoft Certification?

Every Week Receive Resources To Get you Closer to Your Goal!

No Huff! No Fluff! No Spam!

You are on your way to Excelling in Excel and Becoming Microsoft Certified!

Share This

Spread the Good News!

Do me favour, please? Share this with your friends!

Thanks For Commenting!

Awesome for you to comment! Please share the post with your friends.