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

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