Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SUMPRODUCT problem
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:R 500))/SUMPRODUCT((I2:I500="ggo025")*(R2:R500<"")) |
#2
|
|||
|
|||
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 "Jane" schrieb im Newsbeitrag ... 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:R 500))/SUMPRODUCT((I2: I500="ggo025")*(R2:R500<"")) |
#3
|
|||
|
|||
Works great!!!
Thanks you so much. "Frank Kabel" wrote: 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 "Jane" schrieb im Newsbeitrag ... 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:R 500))/SUMPRODUCT((I2: I500="ggo025")*(R2:R500<"")) |
#4
|
|||
|
|||
Why not use more appropriate... =AVERAGE(IF((I2:I500="ggo025")*(R2:R500<""),R2:R5 00)) which you need to confirm with control+shift+enter instead of just with enter. Jane Wrote: 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:R 500))/SUMPRODUCT((I2:I500="ggo025")*(R2:R500<"")) -- Aladin Akyurek ------------------------------------------------------------------------ Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165 View this thread: http://www.excelforum.com/showthread...hreadid=276358 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Row Autofit problem Excel 2003 | Excel Discussion (Misc queries) | |||
SUMPRODUCT Problem | Excel Discussion (Misc queries) | |||
Sumproduct ... Empty Cells vs Spaces? | Excel Discussion (Misc queries) | |||
Paper Tray selection Problem, | Excel Discussion (Misc queries) | |||
SUMPRODUCT - HIERARCHY | Excel Worksheet Functions |