Sumproduct

[Guest]

Hi,

What do I need to add to the below formula, to get the return result as
"zero", when one of the cell in column AO is not "Y". Prensently, the return
result is #DIV/0!.

=SUMPRODUCT(--('ES 0421'!$AC$2:$AC$1903=$B13),--('ES
0421'!$AO$2:$AO$1903="Y"),('ES 0421'!$AI$2:$AI$1903)/A2)

Thanks,
Priscilla

 

[Guest]

You will only get #div/0! if A2 is 0.

 

[Guest]

Yes cell A2 is "zero", is there anyway I can get around that.

 

[Guest]

=IF (A2<>0,SUMPRODUCT(--('ES 0421'!$AC$2:$AC$1903=$B13),--('ES
0421'!$AO$2:$AO$1903="Y"),('ES 0421'!$AI$2:$AI$1903)/A2),0)

this will set cell to 0 if A2 is 0


HTH