What wrong with sumproduct function?

  • Thread starter Thread starter Eric
  • Start date Start date
E

Eric

I would like to sum all numbers, which match following conditions,

=SUMPRODUCT(($B$1816:$B$2400=$A2402),(C$1816:C$2400<0),(C$1816:C$2400))
press ctrl + shift + enter

but it returns zero, and I have checked it, zero should not be the result.
Does anyone have any suggestions what wrong with sumproduct function?
Thanks in advance for any suggestions
Eric
 
1. Sumproduct is not an array function. Use Enter, not Ctrl-Shift-Enter.
2. You must convert false/trues to numbers. One way:
=SUMPRODUCT(($B$1816:$B$2400=$A2402)*(C$1816:C$2400<0)*(C$1816:C$2400))

Regards,
Fred
 
=SUMPRODUCT(($B$1816:$B$2400=$A2402)*(C$1816:C$2400<0)*(C$1816:C$2400))

Remember to Click Yes, if this post helps!
 
I thought I'd already replied to this, but I guess that the message didn't
get out of my outbox.

You don't need Control Shift Enter, but you do need to convert the boolean
TRUE/FALSE to numbers 1/0/ The usual way of doing so is the double unary
minus.
=SUMPRODUCT(--($B$1816:$B$2400=$A2402),--(C$1816:C$2400<0),C$1816:C$2400)
 
Back
Top