Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-12-2012, 08:06 PM
angie.chang angie.chang is offline Sumproduct Windows 7 64bit Sumproduct Office 2010 64bit
Novice
Sumproduct
 
Join Date: Jun 2012
Posts: 9
angie.chang is on a distinguished road
Default 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)))
Reply With Quote
  #2  
Old 06-12-2012, 10:59 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Sumproduct Windows 7 32bit Sumproduct Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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...
Reply With Quote
  #3  
Old 06-14-2012, 08:32 AM
angie.chang angie.chang is offline Sumproduct Windows 7 64bit Sumproduct Office 2010 64bit
Novice
Sumproduct
 
Join Date: Jun 2012
Posts: 9
angie.chang is on a distinguished road
Default 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
Reply With Quote
  #4  
Old 06-14-2012, 10:00 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Sumproduct Windows 7 32bit Sumproduct Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumproduct sumproduct formula to pull info from multiple sheets Berk21 Excel 7 01-15-2012 11:41 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:05 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft