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

[AliceJ]

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 i
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

 

[joeu2004]

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)))

 

[Kevin@Radstock]

Hi

I see you have come up with a different user name and using a differen
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 othe
forums
Is it because you don't understand your question or you don't understan
Excel

For anyone who is interested, here are two of the user names this perso
has used
http://tinyurl.com/a8yf95

http://tinyurl.com/a8yf95

They have also posted at Mr Excel


QUOTE=AliceJ;1608659]Hi, I found many people said it's useful to us
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
1 High 2
2 Low 2
3 High 3