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