Use SUMPRODUCT to multiply ranges
Last month, David H Ringstrom discussed ways in which Excel formulae using SUM functions could be extended by using multiple criteria. This article explains how similar results can be achieved using the SUMPRODUCT function.
Excel's SUMPRODUCT function is one of the spreadsheet program’s less obvious jewels, but those in the know are well aware of its usefulness.
Put very simply, SUMPRODUCT multiplies two ranges together to return a compound total.
As demonstrated below, a single SUMPRODUCT formula can replace the eight formulas listed in Cells D2 through D9. The function works by multiplying the values in Column B by the values in Column C on a row-by-row basis, and then sums the total.
This basic capability is useful, but SUMPRODUCT also can operate like a souped-up version of VLOOKUP or SUMIF.
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.
In this example, 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.
Istead of using SUMIF in this way, however, SUMPRODUCT can be used to return the same results:
In the SUMIF example (previous picture), the result is $396,495. In the lower SUMPRODUCT example, every time the formula found a match in Column D on the word Apples, it added up the corresponding value in Column E. Like VLOOKUP, SUMIF 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.
In this next example, SUMPRODUCT returns $272,584 because we specified that we only want sales for apples sold in North Georgia:
This SUMPRODUCT 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.
In the final example, 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.
"Either you work Excel, or it works you!" says David Ringstrom CPA, the head of Atlanta-based software and database consultancy Accounting Advisors. He presents Excel training webcasts for AccountingWEB partner CPE Link and contributes articles on Excel to Microsoft Professional Accountant's Network newsletter. He can be reached by email at david[AT]acctadv.com.