SUMPRODUCT problem

[Guest]

this formula is returning VALUE#! error, the formula works if data is filled
in through cells 500, but if there are blank cells in the range I get this
error.

I'm trying to find out the average in column R, if column I is "ggo025" and
column R is not "". Please help, thanks.



=SUMPRODUCT((I2:I500="ggo025")*(R2:R500<>"")*(R2:R500))/SUMPRODUCT((I2:I500="ggo025")*(R2:R500<>""))

 

[Frank Kabel]

Hi
try:
=SUMPRODUCT(--(I2:I500="ggo025"),--(R2:R500<>""),R2:R500)/SUMPRODUCT(--
(I2:I500="ggo025"),--(R2:R500<>""))

I suspect you don't have REAL blank cells but maybe a "" string in
R2:R500

--
Regards
Frank Kabel
Frankfurt, Germany

this formula is returning VALUE#! error, the formula works if data is filled
in through cells 500, but if there are blank cells in the range I get this
error.

I'm trying to find out the average in column R, if column I is "ggo025" and
column R is not "". Please help, thanks.

=SUMPRODUCT((I2:I500="ggo025")*(R2:R500<>"")*(R2:R500))/SUMPRODUCT((I2:
I500="ggo025")*(R2:R500<>""))

 

[Guest]

Works great!!!

Thanks you so much.

Hi
try:
=SUMPRODUCT(--(I2:I500="ggo025"),--(R2:R500<>""),R2:R500)/SUMPRODUCT(--
(I2:I500="ggo025"),--(R2:R500<>""))

I suspect you don't have REAL blank cells but maybe a "" string in
R2:R500

--
Regards
Frank Kabel
Frankfurt, Germany


=SUMPRODUCT((I2:I500="ggo025")*(R2:R500<>"")*(R2:R500))/SUMPRODUCT((I2:
I500="ggo025")*(R2:R500<>""))

 

[Aladin Akyurek]

Why not use more appropriate...

=AVERAGE(IF((I2:I500="ggo025")*(R2:R500<>""),R2:R500))

which you need to confirm with control+shift+enter instead of just wit
enter.