Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
General Sumproduct Question
When using Sumproduct as a multiple criteria sumif function, I've seen it
used two ways: sumproduct(()*()*()) sumproduct(--(),--(),()) My question is are these just two ways of doing the same thing, or does one do something the other does not? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
General Sumproduct Question
Essentially, both ways are coercing the TRUE/FALSE result of each criteria to
be 1 (for TRUE) or 0 (for FALSE). Here is a link to a great tutorial on SumProduct. It will answer your question in mucj more detail. http://www.xldynamic.com/source/xld.SUMPRODUCT.html Hope this helps, Hutch "Aaron" wrote: When using Sumproduct as a multiple criteria sumif function, I've seen it used two ways: sumproduct(()*()*()) sumproduct(--(),--(),()) My question is are these just two ways of doing the same thing, or does one do something the other does not? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
General Sumproduct Question
The -- takes a logical value of TRUE or FALSE and makes the value 1 or 0.
The first - coerces Excel to make the logical value for TRUE numeric, i.e. -1. The second - makes the -1 positive - ie. 1. FALSE becomes 0 which is neither negative or positive. Then SUMPRODUCT multiplies the resulting values by the rest of the arguments. The format with the * will cause Excel to make logical values numeric. TRUE becomes 1 and FALSE becomes 0. Then the multiplication is done. The effect of the two formulas is the same. Some people prefer one version over the other. SUMPRODUCT can also add, subtract, divide and exponentiate. Tyro "Aaron" wrote in message ... When using Sumproduct as a multiple criteria sumif function, I've seen it used two ways: sumproduct(()*()*()) sumproduct(--(),--(),()) My question is are these just two ways of doing the same thing, or does one do something the other does not? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
General Sumproduct Question
Yes, they certainly do.
Imagine this data Id Date Amt1 Amt2 Amt3 X 01-Jan 19 13 19 Y 03-Feb 18 20 20 X 01-Feb 18 20 18 Try the two formula and see what happens =SUMPRODUCT(--(A1:A4="X"),--(C1:C410),C1:C4) and =SUMPRODUCT((A1:A4="X")*(C1:C410)*(C1:C4)) Then conversely, try these two =SUMPRODUCT(--(A2:A4="X"),--(MONTH(B2:B4)=1),C2:E4) and =SUMPRODUCT((A2:A4="X")*(MONTH(B2:B4)=1)*(C2:E4)) Horses for courses. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Aaron" wrote in message ... When using Sumproduct as a multiple criteria sumif function, I've seen it used two ways: sumproduct(()*()*()) sumproduct(--(),--(),()) My question is are these just two ways of doing the same thing, or does one do something the other does not? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
General Question | Excel Discussion (Misc queries) | |||
General Question | Excel Discussion (Misc queries) | |||
General Question | Excel Discussion (Misc queries) | |||
General Question | Excel Worksheet Functions | |||
General chart set up question | Charts and Charting in Excel |