What wrong with sumproduct function?

[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

 

[Fred Smith]

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

 

[Ms-Exl-Learner]

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

Remember to Click Yes, if this post helps!

 

[David Biddulph]

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

 

[David Biddulph]

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)