Sumproduct
 

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

Sumproduct
 

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

"Priscilla" wrote:

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

Sumproduct
 

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

"Toppers" wrote:

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

"Priscilla" wrote:

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

Sumproduct
 

=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


"Priscilla" wrote:

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

"Toppers" wrote:

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

"Priscilla" wrote:

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