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.
A previous version of this article first appeared on www.accountingweb.com .
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 david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link
