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