 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / January 2008XL2k7 SUMIFS()
It appears to me that the new SUMIFS() formula in XL2K7 replaces the need for SUMPRODUCT formulas (it seems to be quite faster as well). Is this a correct assumption, or are there certain situations where SUMPRODUCT will handle a calculation SUMIFS will not (for summing, I realize SUMPRODUCT also counts)? Wanted to hear back from the experts before I change over my SUMPRODUCT formulas to SUMIFS. Everyone that would use these reports has XL2K7. TIA, ryanb. You are correct that is a better alternative than say a simple =SUMPRODUCT(--(rng1=num1),--(rng2="value2")) but there are many, many instances where it won't work, anywhere in which a range is manipulated , such as =SUMPRODUCT(--(MONTH(rng)=3),--(YEAR(rng)=2008)) As far SUMPRODUCT counting, don't forget there is a new COUNTIFS in XL2007 as well. If your formulae are of the former type, switching to SUMIFS may be worth your effort, as long as you won't be sharing with users who have an earlier version.  Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > It appears to me that the new SUMIFS() formula in XL2K7 replaces the need > for SUMPRODUCT formulas (it seems to be quite faster as well). Is this a [quoted text clipped - 6 lines] > TIA, > ryanb. Thanks Bob! I had yet to experiment with range manipulation in SUMIFS() formulas... saved me a ton of time. ryanb. > You are correct that is a better alternative than say a simple > [quoted text clipped - 22 lines] >> TIA, >> ryanb. SUMPRODUCT can do more than just sum products, if you use operators for example, such as * (multiply), / (divide), - (subtract) + (addition) and ^ (exponentiation), it will do as told. If you have 3 numbers in A1:A3 and you want to divide them by 3 numbers in B1:B3 and sum the quotients then =SUMPRODUCT(A1:A3/B1:B3) which is the same as =A1/B1 + A2/B2 +A3/B3. . =SUMPRODUCT(A1:A3+B1:B3) is the same as =(A1+B1)+(A2+B2)+(A3+B3) =SUMPRODUCT(A1:A3-B1:B3) is the same as =(A1-B1) + (A2-B2) + (A3-B3). =SUMPRODUCT(A1:A3^B1:B3) is the same as =A1^B1 + A2^B2 + A3^B3. And it may do other things. I found no documentation in Excel 2007 help that SUMPRODUCT does these things. All help says is that SUMPRODUCT sums the products. That and more! Tyro > It appears to me that the new SUMIFS() formula in XL2K7 replaces the need > for SUMPRODUCT formulas (it seems to be quite faster as well). Is this a [quoted text clipped - 6 lines] > TIA, > ryanb. See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation. Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > SUMPRODUCT can do more than just sum products, if you use operators for > example, such as * (multiply), / (divide), - (subtract) + (addition) and ^ [quoted text clipped - 20 lines] >> TIA, >> ryanb. >All help says is that SUMPRODUCT sums the products. And MS leaves it to your imagination as to how you get the products! There are multitudes of ways. Next to IF, SUMPRODUCT may be the most versatile function there is. SignatureBiff Microsoft Excel MVP > SUMPRODUCT can do more than just sum products, if you use operators for > example, such as * (multiply), / (divide), - (subtract) + (addition) and ^ [quoted text clipped - 20 lines] >> TIA, >> ryanb. Acutally SUMPRODUCT is a misnomer. SUMPRODUCT does as told, eg =SUMPRODUCT(A1:A2^B1:B2) is same as =(A1^B1)+(A^B2). There are no products here at all. Products are produced by multiplication. What we're doing here is exponentiation. Perhaps, in this particular case, the function should be SUMEXPONENTIATION and of course =SUMPRODUCT(A1:A2/B1:B2) would be SUMQUOTIENT etc. Tyro > >All help says is that SUMPRODUCT sums the products. > [quoted text clipped - 27 lines] >>> TIA, >>> ryanb. >=(A1^B1)+(A^B2). There are no products Actually there is. The product (which can also be described as the argument) is the result of (A1^B1)+(A^B2). It's a single product (argument). At least, that's how I interpret it. SignatureBiff Microsoft Excel MVP > Acutally SUMPRODUCT is a misnomer. SUMPRODUCT does as told, eg > =SUMPRODUCT(A1:A2^B1:B2) is same as =(A1^B1)+(A^B2). There are no products [quoted text clipped - 36 lines] >>>> TIA, >>>> ryanb. When I went to school, products were produced by multiplication. Division produced quotients and remainders, Summing produced sums and subtraction produced differences. Perhaps things have changed. Tyro > >=(A1^B1)+(A^B2). There are no products > [quoted text clipped - 45 lines] >>>>> TIA, >>>>> ryanb. I know what you mean! The product of 10*10 is 100. What is the product of 10? Is it 10 or 0? According to Excel: =PRODUCT(10) = 10 I'm thinking that under the covers SUMPRODUCT is doing something like this when there's a single argument: arg * 1 So there is a product. Of course, I could be wrong! SignatureBiff Microsoft Excel MVP > When I went to school, products were produced by multiplication. Division > produced quotients and remainders, Summing produced sums and subtraction [quoted text clipped - 51 lines] >>>>>> TIA, >>>>>> ryanb. You are right. In algebra 10 = (1)*10. Therefore (1)*10 =10 In algebra all numbers are presumed to be muliplied by 1. Fundamental law of algebra. Therefore -10^2 = -100. But Excel in its comformity to Lotus 1-2-3 and its conformity to VisiCalc and its error computes -10^2 as 100. VBA computes the correct answer as: -100. Because: -10^2= -(1)10^2. Exponentiation is done before applying the unary minus. >I know what you mean! > [quoted text clipped - 68 lines] >>>>>>> TIA, >>>>>>> ryanb. If you look at an =sumproduct() formula with one argument, like: =sumproduct(a1:a2) you could see it as that one argument multiplied by an identity (a value/array/matrix). Much like =3 is the same as =3*1 or =3+0 So I'd consider: =SUMPRODUCT(A1:A2^B1:B2) this same kind of =sumproduct() formula--one that has a single argument (an array in this case) with an implied second identity argument: =SUMPRODUCT(A1:A2^B1:B2,{1;1}) > Acutally SUMPRODUCT is a misnomer. SUMPRODUCT does as told, eg > =SUMPRODUCT(A1:A2^B1:B2) is same as =(A1^B1)+(A^B2). There are no products [quoted text clipped - 40 lines] > >>> TIA, > >>> ryanb. SignatureDave Peterson So, if you don't want to product them, use an array SUM formula, the exponentiation comes from within. Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Acutally SUMPRODUCT is a misnomer. SUMPRODUCT does as told, eg > =SUMPRODUCT(A1:A2^B1:B2) is same as =(A1^B1)+(A^B2). There are no products [quoted text clipped - 36 lines] >>>> TIA, >>>> ryanb. There is nothing intrinsically versatile in SUMPRODUCT. The versatility arises from the fact that multiplying TRUE by TRUE gives 1, etc. Because of this we have arrays we can sum, but we don't necessarily need SP, an array SUM does it just as well in the vast majority of cases. Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > >All help says is that SUMPRODUCT sums the products. > [quoted text clipped - 27 lines] >>> TIA, >>> ryanb. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.