Some unlikely uses for Excel's SUMPRODUCT function
by David Ringstrom on
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 H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at firstname.lastname@example.org or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.
You may like these other stories...
Cybersecurity is no longer the domain of an organization's IT staff. It's moved to the boardroom, and in a big way. Accountants and financial managers may have been thinking it's just the province of the tech...
You probably don't want to think about how many times you access the File menu in Excel 2010 or 2013. Personally I think Excel 2010 has the best possible File menu arrangement, other than having Print Preview grafted...
Following other recent high-profile hacking events, investigators discovered yesterday that hackers broke into the draft work paper files of several famous CPA firms. Revealing images of the scantily clad documents have been...
Upcoming CPE Webinars
In this jam-packed presentation Excel expert David Ringstrom, CPA will give you a crash-course in creating spreadsheet-based dashboards. A dashboard condenses large amounts of data into a compact space, yet enables the end user to easily drill down into details when warranted.
This webcast will include discussions of important issues in SSARS No. 19 and the current status of proposed changes by the Accounting and Review Services Committee in these statements.
Kristen Rampe will share how to speak and write more effectively by understanding your own and your audience's communication style.
Amber Setter will show the value of leadership assessments as tools for individual and organizational leadership development initiatives.