Count Conditional Distinct Values

**Gos-C** · 02-10-2013, 05:16 AM

Hi all,

I am using the following array formula to count distinct values in column A where the first two characters is "12" and corresponding cell in another column E is not blank:

PHP Code:


=SUM(IF((LEFT($A$2:$A$67,2)="12")*($E$2:$E$67<>""),1/COUNTIF($A$2:$A$67,$A$2:$A$67)))

But it is not giving the correct result - see attachment. It should be 63 instead of 62, but I can't figure out why.

Any help, please?

Thank you,
Gos-C

**Andrew-R** · 02-10-2013, 05:29 AM

The COUNTIF you're dividing by isn't conditional on column E being empty, so the values on rows 7 and 66 are being counted as 1/2 each, rather than 1 each.

You can fix this by using a COUNTIFS as the divisor:

=SUM(IF((LEFT($A$2:$A$67,2)="12")*($E$2:$E$67<>""),1/COUNTIFS($A$2:$A$67,$A$2:$A$67,E2:E67,"<>")))