Editor’s Note: 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.

Fig 1.

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.

Fig 2.

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).

Fig. 3

If you multiply 200 by True, the True value will be evaluated as a 1, and the result will be 200. If you multiply 200 by False, the False value will be evaluated as a 0, and the result will be 0.

It Works in the Worksheet, But Not in SUMPRODUCT!
The calculation shown in Figure 3 works correctly in the worksheet. If you built some formulas that generate True/False values and then actually multiply those values, you will get the correct result shown in Figure 3. Unfortunately, Figure 3 contains 71 formulas. It would be nice to solve the calculation with a single formula.

Although the Excel worksheet knows that True x 2 is 2, the SUMPRODUCT function will not respect this calculation. Figure 4 (below) shows the Evaluate Formula window after 15 steps of calculation. In the next step, the entire formula evaluates to 0. For some reason, True x 2 in SUMPRODUCT is 0 instead of 2.

Fig. 4

If you read the Excel help section for SUMPRODUCT, the “Remarks” section notes that any nonnumeric values in the arrays render as if they were 0. While this makes sense for Text values, I don’t know why Microsoft extended this to True/False values as well.

One of Two Clever Workarounds
Someone discovered that there are two methods for coaxing the array of True/False results into an array of 1’s and 0’s.

One method is to precede each Boolean array in the SUMPRODUCT with a minus-minus. The unary minus operation changes the True values to -1 and the False values to 0. The second unary minus changes the True values back to 1.

The new formula is shown in Figure 5 (below). You can see in cell A3 that this formula calculates correctly. The Evaluate Formula dialog shows the calculation after the first two Boolean arrays have been converted to 1’s and 0’s. The third Boolean array is about to be converted.

Fig. 5

The other method is to replace the commas between the Boolean arrays with asterisks. The asterisk forces the Boolean arrays to be multiplied together using the same calculation engine as in Figure 3, instead of the SUMPRODUCT function. As shown in Figure 6 (below), the three Boolean arrays change into a single array of 1’s and 0’s. The result in A3 is also correct.

Using SUMPRODUCT to Solve Last Week’s Question
Back to Jerome’s comment from last week: why not use SUMPRODUCT instead of an array formula to count or sum the number of records in each month?

Figure 7 (below) shows a SUMPRODUCT formula to calculate the total amount in column F.

Figure 8 (also below) shows a SUMPRODUCT to count the number of records in column E. In this formula, there is only a single Boolean array. The minus-minus changes the True values to 1. Personally, I think this is much harder to explain than the formula from last week. However, Jerome is correct that no one will have trouble editing this formula without knowing the Ctrl Shift Enter keystroke.

Fig. 6

Fig. 7

Fig. 8

Bill Jelen is the author of 32 books about Excel, including Guerilla Data Analysis Using Microsoft Excel. You can win a copy of one of his books if your question is selected for a column. Post your question to the CFO Community Center that appears to the right of this article.