| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
| What's wrong with this IF function. | Excel Discussion (Misc queries) | |||
| What is Wrong with this function?????????? | Excel Worksheet Functions | |||
| What am I doing wrong with PMT function? | Excel Discussion (Misc queries) | |||
| SUMPRODUCT is showing wrong Amount | Excel Discussion (Misc queries) | |||
Hybrid Mode
