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}`

RECOMMENDATION:

Watch these Videos on FREQUENCY

## Seeking Microsoft Certification?

No Huff! No Fluff! No Spam!