To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com’s Spreadsheet Tips Page.
Reader Jerome K. wins an autographed copy of Learn Excel 97-2007 from MrExcel for his comment on last week’s article (see “Spreadsheets: Many Formulas into One with a Top-Secret Move”). Jerome wondered why I would suggest the arcane array formula, which requires Ctrl Shift Enter, when the SUMPRODUCT function would have worked. Jerome noted that SUMPRODUCT is “easier to explain to colleagues, and easier for them to edit as well.”
Personally, I think it is easier to teach someone to use Ctrl Shift Enter, instead of explaining why you have to use minus-minus to coax an array of True/False values into an array of 1′s and 0′s. However, this week, I will explain how SUMPRODUCT can be hijacked to solve the same problem as last week.
The Original Use of SUMPRODUCT
SUMPRODUCT is designed to multiply corresponding components in the given arrays and return the sum of those products. For a more concrete example, consider the small data set in Figure 1 (below, left).
Range B2:B7 contains a series of item quantities. Range C2:C7 contains the unit price for each item. To figure out the total value, you could use =SUMPRODUCT(B2:B7,C2:C7). This function will multiply B2 x C2, B3 x C3, and so on, totaling the products. This is a clever function that prevents you from having to add a new series of =B2*C2 formulas in column D in order to calculate the total.
The Boolean Use of SUMPRODUCT
Two useful functions — SUMIF and COUNTIF — were introduced in Excel 97. Until Excel 2007 added SUMIFS and COUNTIFS, there wasn’t a good way to do a SUMIF when you needed to test for multiple conditions.
To work around this limitation, people began using Boolean arrays as part of the SUMPRODUCT function. Figure 2 (below) shows the concept behind the formula. The first array checks to see if the regions in A6:A19 are equal to West in cell A1. The second array checks to see if the products in B6:B19 are equal to Gadget in B1. The third array checks to see if the industries in C6:C19 are equal to Retail in C1. The fourth array is the amounts in D6:D19.
The first three arrays in that formula evaluate a series of True/False values. To picture the calculation Excel would produce, build a temporary calculation that can be held in memory that looks something like Figure 3 (below).