OR in SUMPRODUCT formula
On Jun 13, 9:11*am, Walter Briscoe
wrote:
=SUMPRODUCT(($R19:$AC19<0)*(($R$34:$AC$34<0)+($ R$35:$AC$35<0)0))
[....]
1. When using "+" for OR in this context, generally you
should test the sum for "0".
Why? I prefer 0 as a synonym for FALSE and think of TRUE as < 0.
This isn't about preference; it's about correctness. I guess my
explanation in the subsequent paragraph was not clear.
If both $R$34:$AC$34<0 and $R$35:$AC$35<0 are true, then ($R$34:$AC
$34<0)+($R$35:$AC$35<0) is 2, not 1.
If $R19:$AC19<0 is also true, then ($R19:$AC19<0)*(($R$34:$AC$34<0)+
($R$35:$AC$35<0)) is 2, not 1.
Thus, SUMPRODUCT would double-account (2 instead of 1) for the one
condition.
I assumed that you want to count only once when "x AND (y OR z)" is
true.
However, if you want to count "x AND y" and "x AND z" separately, then
the "0" should indeed be omitted.
|