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.
About the author
"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.