Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT with )--
Hi,
I know what -- does however I have seen it a couple of ways, syntax sumproduct(--(criteria),--(criteria)) and also sumproduct(--(criteria)--(criteria)) what is the difference in using the , or not? thx |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT with )--
If you use the unary minuses you would always need a comma as a delimiter so
your second example is wrong. You can also use sumproduct((.....)*(....)) http://www.mcgimpsey.com/excel/formulae/doubleneg.html -- Regards, Peo Sjoblom "Paul Dennis" wrote in message ... Hi, I know what -- does however I have seen it a couple of ways, syntax sumproduct(--(criteria),--(criteria)) and also sumproduct(--(criteria)--(criteria)) what is the difference in using the , or not? thx |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT with )--
As an example I have
=SUMPRODUCT(--(ProblemClosedDate=$B21)--(ActionClosedDate=$C21),--(ProblemClosedDate<""),--(ProblemOpenDate<$F20),--(LOB=$E21),--(DueDateVersion$D21)) and it returns 21. I have checked my numbers and the result matches what I want. If I however add a comma after the first criteria then it returns 0 which is incorrect. My initial though was the , changed the criteria from an additional filter to an and i.e. both the criteria had to match. "Peo Sjoblom" wrote: If you use the unary minuses you would always need a comma as a delimiter so your second example is wrong. You can also use sumproduct((.....)*(....)) http://www.mcgimpsey.com/excel/formulae/doubleneg.html -- Regards, Peo Sjoblom "Paul Dennis" wrote in message ... Hi, I know what -- does however I have seen it a couple of ways, syntax sumproduct(--(criteria),--(criteria)) and also sumproduct(--(criteria)--(criteria)) what is the difference in using the , or not? thx |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT with )--
That version cannot be correct. This
--(ProblemClosedDate=$B21)--(ActionClosedDate=$C21) will return an array of 0, 1 and instead of two arrays of 0, 1. As the arrays then get multiplied by other arrays, some items can/will get double counted because of the 2s. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Paul Dennis" wrote in message ... As an example I have =SUMPRODUCT(--(ProblemClosedDate=$B21)--(ActionClosedDate=$C21),--(ProblemClosedDate<""),--(ProblemOpenDate<$F20),--(LOB=$E21),--(DueDateVersion$D21)) and it returns 21. I have checked my numbers and the result matches what I want. If I however add a comma after the first criteria then it returns 0 which is incorrect. My initial though was the , changed the criteria from an additional filter to an and i.e. both the criteria had to match. "Peo Sjoblom" wrote: If you use the unary minuses you would always need a comma as a delimiter so your second example is wrong. You can also use sumproduct((.....)*(....)) http://www.mcgimpsey.com/excel/formulae/doubleneg.html -- Regards, Peo Sjoblom "Paul Dennis" wrote in message ... Hi, I know what -- does however I have seen it a couple of ways, syntax sumproduct(--(criteria),--(criteria)) and also sumproduct(--(criteria)--(criteria)) what is the difference in using the , or not? thx |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT with )--
Two unary minus signs is the same as a plus sign, but affixing a plus sign
to an expression does not force a calculation to take place. When no calculations are being done to a logical expressions (or a number that is being returned as text), the double unary forces a calculation (minus one times minus one times the TRUE or FALSE returned by the logical expression) to take place... that is why it is used. The first argument in the SUMPRODUCT function in the formula you posted does, in fact, have a calculation taking place, so the double unary minus signs are unnecessary. This should return the same thing your formula does... =SUMPRODUCT((ProblemClosedDate=$B21)+(ActionClose dDate=$C21),--(ProblemClosedDate<""),--(ProblemOpenDate<$F20),--(LOB=$E21),--(DueDateVersion$D21)) although I have to admit that looks like an odd construction to me. Are you sure there is not missing comma between the first two arguments of the SUMPRODUCT function in the formula as you originally posted it? Rick "Paul Dennis" wrote in message ... As an example I have =SUMPRODUCT(--(ProblemClosedDate=$B21)--(ActionClosedDate=$C21),--(ProblemClosedDate<""),--(ProblemOpenDate<$F20),--(LOB=$E21),--(DueDateVersion$D21)) and it returns 21. I have checked my numbers and the result matches what I want. If I however add a comma after the first criteria then it returns 0 which is incorrect. My initial though was the , changed the criteria from an additional filter to an and i.e. both the criteria had to match. "Peo Sjoblom" wrote: If you use the unary minuses you would always need a comma as a delimiter so your second example is wrong. You can also use sumproduct((.....)*(....)) http://www.mcgimpsey.com/excel/formulae/doubleneg.html -- Regards, Peo Sjoblom "Paul Dennis" wrote in message ... Hi, I know what -- does however I have seen it a couple of ways, syntax sumproduct(--(criteria),--(criteria)) and also sumproduct(--(criteria)--(criteria)) what is the difference in using the , or not? thx |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT with )--
It's impossible to say without knowing what you want this formula to do
(--(ProblemClosedDate=$B21)--(ActionClosedDate=$C21) is the same as ((ProblemClosedDate=$B21)+(ActionClosedDate=$C21 ) so if the first range returns 3 TRUE and the second 2 TRUE it will total up to 5 so it is similar to an OR function not an AND meaning that if ProblemClosedDate=$B21 returns FALSE for one particular row/column and ActionClosedDate returns TRUE for the same particular row/column it will still count as 1 while (--(ProblemClosedDate=$B21),--(ActionClosedDate=$C21) will return zero for the same, to get 1 for the latter both criteria need to be TRUE so it would need 2, SUMPRODUCT(--(ProblemClosedDate=$B21)OR(ActionClosedDate=$C21 )AND(ProblemClosedDate<"")AND(ProblemOpenDate<$F2 1)AND(LOB=$E21)AND(DueDateVersion$D21)) meaning that if the first 2 criteria are TRUE they will count as 2 as long as the other criteria are TRUE, if one is TRUE and the other is FALSE they will count as 1, if you would use the comma between the first 2 both would need to be TRUE to count as 1 -- Regards, Peo Sjoblom "Paul Dennis" wrote in message ... As an example I have =SUMPRODUCT(--(ProblemClosedDate=$B21)--(ActionClosedDate=$C21),--(ProblemClosedDate<""),--(ProblemOpenDate<$F20),--(LOB=$E21),--(DueDateVersion$D21)) and it returns 21. I have checked my numbers and the result matches what I want. If I however add a comma after the first criteria then it returns 0 which is incorrect. My initial though was the , changed the criteria from an additional filter to an and i.e. both the criteria had to match. "Peo Sjoblom" wrote: If you use the unary minuses you would always need a comma as a delimiter so your second example is wrong. You can also use sumproduct((.....)*(....)) http://www.mcgimpsey.com/excel/formulae/doubleneg.html -- Regards, Peo Sjoblom "Paul Dennis" wrote in message ... Hi, I know what -- does however I have seen it a couple of ways, syntax sumproduct(--(criteria),--(criteria)) and also sumproduct(--(criteria)--(criteria)) what is the difference in using the , or not? thx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
Sumproduct | Excel Discussion (Misc queries) | |||
SUMPRODUCT | Excel Worksheet Functions | |||
Using SumProduct in VB | Excel Discussion (Misc queries) | |||
Sumproduct | Excel Worksheet Functions |