What wrong with sumproduct function?
 

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

=SUMPRODUCT(($B$1816:$B$2400=$A2402),(C$1816:C$240 0<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

What wrong with sumproduct function?
 

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$240 0<0)*(C$1816:C$2400))

Regards,
Fred

"Eric" wrote in message
...
I would like to sum all numbers, which match following conditions,

=SUMPRODUCT(($B$1816:$B$2400=$A2402),(C$1816:C$240 0<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

What wrong with sumproduct function?
 

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

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Eric" wrote:

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

=SUMPRODUCT(($B$1816:$B$2400=$A2402),(C$1816:C$240 0<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

What wrong with sumproduct function?
 

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

"Eric" wrote in message
...
I would like to sum all numbers, which match following conditions,

=SUMPRODUCT(($B$1816:$B$2400=$A2402),(C$1816:C$240 0<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

What wrong with sumproduct function?
 

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)
--
David Biddulph


"Eric" wrote in message
...
I would like to sum all numbers, which match following conditions,

=SUMPRODUCT(($B$1816:$B$2400=$A2402),(C$1816:C$240 0<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