BobA said:
What am I doing wrong?
=SUMPRODUCT(--(C11:C1000=J2),(D11
1000=I2),(F11:F1000))
First, that sums F11:F1000 based on only two criteria, not three. Did you
mean for that last term to be a conditional expression, e.g. F11:F1000=K2?
Second, what you might have done wrong is omit the double-negative (--)
before the second term, just as you have before the first term. Or perhaps
you intended to write:
=SUMPRODUCT((C11:C1000=J2)*(D11
1000=I2),F11:F1000)
or
=SUMPRODUCT((C11:C1000=J2)*(D11
1000=I2)*(F11:F1000=K2))
To explain.... C11:C1000=J2, for example, returns an array of logic values,
TRUE or FALSE. SUMPRODUCT "treats array entries that are not numeric as if
they were zeros". Consequently, you are multiplying everything by zero,
which explains why SUMPRODUCT returns zero.
TRUE and FALSE are converted to the values 1 and 0 when they are included in
an arithmetic expression. That is why we might write --(C11:C1000=J2): the
double-negative is (two) arithmetic operations.
Likewise, (C11:C1000=J2)*(D11
1000=I2) is one arithmetic operation
(multiply). So writing double-negative (--) in front of that, as some
people do, is unnecessary (and costly).
Nonetheless, alternatively you can write:
=SUMPRODUCT(--(C11:C1000=J2),--(D11
1000=I2),F11:F1000)
or
=SUMPRODUCT(--(C11:C1000=J2),--(D11
1000=I2),--(F11:F1000=K2))