#1
|
|||
|
|||
Sumproduct
What is the difference using ,(comma) vs using *(multiplication)?
What is the purpose of using --(double negative) Why sumproduct does not work if I change from z6 to "Yes" ($G$7:$G$450=Z6) to ($G$7:$G$450="Yes")? =SUMPRODUCT(--($C$7:$C$450=X7), --($B$7:$B$450<=AC3), --($B$7:$B$450>=Z3), --($G$7:$G$450=”Yes”), $M$7:$M$450) =SUMPRODUCT(--($C$7:$C$450=X7), --($B$7:$B$450<=AC3), --($B$7:$B$450>=Z3), --($G$7:$G$450=Z6), $M$7:$M$450) SUMPRODUCT((C7:C450=X7)*((--$B$7:$B$450<=AC3)*(--($B$7:$B$450>=Z3))*(G7:G450=Z6)*(M7:M450))) |
#2
|
||||
|
||||
Hi Angie,
Lots of good questions there. They all take a bit of explaining so I'll point you to two articles to read and then, once you've read them, we can discuss any outstanding questions you have? http://www.xldynamic.com/source/xld.SUMPRODUCT.html http://www.xtremevbtalk.com/showthread.php?t=296012 (particularly posts #5 and #7) Let us know how you get on... |
#3
|
|||
|
|||
Sumproduct
Hi Colin,
I think I got it. When using sumproduct for coutifs I can usd the * or --. When using sumproduct as sumif I can use the -- but if I am using * I need to have at least two conditions and excluding the column name heading. Let me know. Still learning the sumproduct functions. Thanks, Angie |
#4
|
||||
|
||||
Hi Angie,
There are a few more subtleties but what you've said is a good starting point. The bottom line is that, in most situations, they're interchangeable and it's just down to the user's preference. But there are some occasions when it's better (or necessary) to use one or the other, including handling column headers (text), text within a sum range and matrices of different sizes. It's also worth remembering that the "IF" and "IFS" functions (such as COUNTIF, COUNTIFS, SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS) and database functions (DCOUNT, DSUM etc) are a lot faster to calculate than SUMPRODUCT, so you should use them when possible. |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
sumproduct formula to pull info from multiple sheets | Berk21 | Excel | 7 | 01-15-2012 11:41 AM |