Some unlikely uses for Excel's SUMPRODUCT function
Posted by AccountingWEB in Technology, Excel on 03/23/2010 - 18:25
By David H. Ringstrom, CPA
While you may not be aware of Excel's SUMPRODUCT function, those in the know often use it for its stated purpose: multiplying two ranges together and then summing the total.
As shown in Figure 1, a single formula using SUMPRODUCT can replace the eight formulas listed in Cells D2 through D9. The SUMPRODUCT function multiplies the values in Column B by the values in Column C on a row-by-row basis, and then sums the total.
Although this is helpful, you might not be aware that SUMPRODUCT also can function like VLOOKUP or SUMIF on steroids.
Figure 1: SUMPRODUCT multiplies values together and sums the products, but can also do more.
Excel's VLOOKUP function allows you to look up data from a table based on criteria that you specify, and has four arguments:
- lookup_value – This is the data that you want to look for in the first column of the table array.
- table array – The table array is a range of two or more columns.
- col_index_num – This argument allows you to specify the column within the table array for which you want to return data.
- range_lookup – In this position you indicate FALSE if you're seeking an exact match for the lookup_value, or TRUE if you want an approximate match. For instance, you'd use FALSE to look up the price of an inventory item, or TRUE if you're determining which tax bracket an income level falls into.
Figure 2: VLOOKUP allows you to look up a single value based on a single criteria.
As shown in Figure 2, VLOOKUP returns $85,106 from Column E for the first instance of Apples that appears in Column D. The additional instances of Apples are ignored. If you need to add up multiple values based on specific criteria, then SUMIF is a better choice. This function has three arguments:
- range – This is a column or row where you want to look for specified criteria.
- criteria – This is the same as the lookup_value for VLOOKUP.
- sum_range – This is a column or row from which you want to add up numbers whenever the criteria is found in the range.
Figure 3: SUMIF allows you to add up multiple items, but also is limited to a single criteria.
Figure 4: SUMPRODUCT can return the same results as SUMIF.
As shown in Figure 3, SUMIF returns $396,495. In this case, every time it found a match in Column D on the word Apples, it added up the corresponding value in Column E. Like VLOOKUP, you can only search based on a single criteria. Fortunately, SUMPRODUCT allows you to add up values based on multiple criteria.
However, let's first use SUMPRODUCT to match based on a single criteria, as shown in Figure 4. In this case it returns the same result as SUMIF because we only provided a single criteria.
Conversely, in Figure 5, SUMPRODUCT returns $272,584 because we specified that we only want sales for apples sold in North Georgia.
Figure 5: SUMPRODUCT also can return results based on multiple criteria.
As shown in Figure 5, the formula looks at Cells A2 through A19 for the words North GA, and at Cells D2 through D19 for the word Apples. When both criteria are met, SUMPRODUCT adds up the corresponding values from Cells E2 through E19.
Figure 6: SUMPRODUCT also can return the number of matches that meet criteria you specify.
Finally, as shown in Figure 6, if you only specify criteria and omit the range to sum, then SUMPRODUCT returns the number of items that match the criteria that you specify.
About the author:
David Ringstrom, CPA, heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm. Contact David at david@acctadv.com.
Related articles:
- 13570 reads
Tags
Accounting and Auditing
Administrative
AICPA
Auditing
Busy Season Daily Workplace Exercises
Careers
Celebrity news
CFO
Consulting
Conversations
Economy
Education
Education and careers
Excel
Excel tips
FASB
Financial Reporting
Firm news
Government
Guest articles
Health care
Human Resources
IFRS
Income tax
International
IRS
IT
Legal issues
Legislation
Marketing
Mergers
PCAOB
Personal Finance
Practice Development
Practice Management
QuickBooks
Retirement
Sarbanes-Oxley
SEC
Self-Improvement
State and Local
Students
Tax
Technology
Training
Trends
Watchdog
Workplace Fitness
Workshops
XBRL
Gail Perry, CPA
I agree!
Once you get acclimated to the ribbon, Excel 2007 is far superior to previous versions of Excel, and Excel 2010 adds some great improvements, most notably the ability to easily customize the ribbon.
Less than that
I think it's worse than you think David. I have been presenting training courses or parts of training courses on Excel for quite a while now and my non random sample shows me that only about 25 - 30% of business users of Excel use Excel 2007. However, I have stopped training with Excel 2003 as I am doing my best to get everyone to realise that Excel 2007 is a lot better than Excel 2003. And from the little time I have had to play with Excel 2010, my impression is that it is another step better than Excel 2007.
Thanks for that comment though David as I can't help but agree that it is a valid one!
Duncan
SUMIFS
Thanks for the great contribution, Duncan. My observation is that about 40% to 50% of users are still using Excel 2003 or earlier, so I crafted the article to be helpful to all Excel users. Your write-up on SUMIFS is a helpful addition for anyone using Excel 2007 or 2010. Keep in mind, though, that workbooks using that function will return errors when opened with earlier versions of Excel. However, in cases where a workbook will only be used in Excel 2007 or later, both SUMIFS and COUNTIFS are great tools to have in one's Excel toolbox.
Good David and have you also considered this?
I am sure you know this David and so far I have looked at just a few things you have prepared but Excel 2007 provides us with the excellent SUMIFS function. I set up your example and put your SUMPRODUCT example alongside a SUMIFS solution just for comparison: see link below to my blog and from there to the Excel 2007 file I have created for this.
I think in this case SUMPRODUCT and SUMIFS are about on a par with each other in terms of perceived complexity of programming. Extending your example to the limit, I asked the two functions to add together sales based on
Region and City and Chain and Product
Here are my results:
=SUMPRODUCT((A2:A19=A22)*(B2:B19=B22)*(C2:C19=C22)*(D2:D19=D22)*E2:E19)
=SUMIFS(E2:E19,A2:A19,A25,B2:B19,B25,C2:C19,C25,D2:D19,D25)
I have discussed these ideas in my Excel 2007 Blog here: http://excel2007master.wordpress.com/ where there is a link to a downloadable Excel 2007 that goes with that blog entry.
I hope you find this contribution as useful as your original article, David.
Best wishes
Duncan