Array formula - it works... but

[David Epstein]

I wrote the array formula below and it works just as needed - counting unique values on the tracking num field.

={SUM(IF((Table1[Division]=$A$1)*(Table1[Phase]=$B$2)*(Table1[Region]=A5)*(Table1[Tracking Num]<>""),1/COUNTIFS(Table1[Division],$A$1,Table1[Phase],$B$2,Table1[Region],A5,Table1[ Tracking Num],"<>",Table1[Tracking Num],Table1[Tracking Num]&"")))}

The "if" defines the 4 conditions. The "countifs" repeats the same 4 conditions. Why do I need the "if" given the "countif" repeats the same conditions?


Thank you,
Dave

 

[NARAYANK991]

Hi Dave ,

The COUNTIFS function returns an array of values ; where the conditions are satisfied , there will be non-zero values , and where the conditions are not satisfied , there will be zeros.

When we take the reciprocal of this array , the zeros will all generate the #DIV/0! error value.

Obviously , using the SUM on these values will return the same error value.

When we use the IF function with the same checks , we get a second array of values , with TRUE where the conditions are satisfied , and FALSE otherwise.

Since the IF function has been used with only two parameters , Excel uses the default value of FALSE where the conditions are not satisfied.

When we use the IF and the COUNTIFS in combination , only those non-zero values are used where the IF function returns the TRUE values ; where the IF function conditions are not satisfied , the logical value FALSE is used.

The SUM function ignores logical values , and hence we get the correct output.

Narayan