sumproduct() function resulted in an error #DIV/0
 

Hi, I found many people said it's useful to use sumproduct() function. In my case, I used it as below. Very bad results Can you correct it ONLY using sumproduct() function?

=SUMPRODUCT(--(A1:A1000="High"),--(C1:C1000<0),B1:B1000/C1:C1000)

A B C
1 High 2 1
2 Low 2 2
3 High 3 2

sumproduct() function resulted in an error #DIV/0
 

"AliceJ" wrote:
I used it as below. Very bad results Can you correct it
ONLY using sumproduct() function?
=SUMPRODUCT(--(A1:A1000="High"),--(C1:C1000<0),B1:B1000/C1:C1000)
A B C
1 High 2 1
2 Low 2 2
3 High 3 2

There might be multiple problems. The root cause might be that some of
C1:C1000 are empty cells. As such, Excel treats them as zero in this
context. That precipitates the #DIV/0 error obviously.

You might think the conditional test --(C1:C1000<0) prevents the evaluation
of corresponding B1:B1000/C1:C1000, but it does not.

Instead, Excel creates an array 1 or 0 (true or false) based on the
evaluation of --(C1:C1000<0). And Excel creates an array of values
B1:B1000/C1:C1000. Then SUMPRODUCT multiplies the corresponding elements of
each array. We don't get that far because of the #DIV/0 errors when
creating the last array.

Try the following array-entered formula instead (press ctrl+shift+Enter
instead of just Enter):

=SUM(IF(A1:A1000="High",IF(C1:C1000<0,B1:B1000/C1:C1000)))

Hi

I see you have come up with a different user name and using a different forum.
You have posted the same question twice to my knowledge in ExcelForum. com and answers have been posted. But you still try to post on other forums.
Is it because you don't understand your question or you don't understand Excel!

For anyone who is interested, here are two of the user names this person has used!
http://www.excelforum.com/excel-form...-is-wrong.html

http://www.excelforum.com/excel-form...-is-wrong.html

They have also posted at Mr Excel.




QUOTE=AliceJ;1608659]Hi, I found many people said it's useful to use sumproduct() function. In my case, I used it as below. Very bad results Can you correct it ONLY using sumproduct() function?

=SUMPRODUCT(--(A1:A1000="High"),--(C1:C1000<0),B1:B1000/C1:C1000)

A B C
1 High 2 1
2 Low 2 2
3 High 3 2[/quote]