Many CPAs, frustrated by rigid and inadequate reports from their general ledger or other enterprise systems, turn to Microsoft Excel. Nimble but powerful, Excel often manipulates data faster and more effectively than less agile applications. But to perform certain tasks optimally, a CPA sometimes may have to bypass what apparently is Excel’s most relevant function and instead use another Excel function that at first may not seem suitable. This article presents such an instance, comparing the SUMIF and SUMPRODUCT functions and demonstrating an innovative approach that can produce the reports you need, quickly and easily.
Let’s begin by automating a simple but tedious and potentially error-prone data analysis and reporting process. Here, a well-known Excel function does the job perfectly. Later, we’ll look at a harder task that requires a more complex—but very workable—Excel solution.
Editor's note: Click here to download this article's sample Excel worksheets. Each of the article's four exhibits is an individual worksheet in a single downloadable Excel file.
Say you want to calculate the total sales for each member of a team, but your GL or other enterprise system can’t do the job. So you export the relevant data into Excel, where you use the SUMIF function [SUMIF (range, criterion, sum_range)] to cull and add up the sales transactions for each salesperson. It’s clear this function can save a lot of work by automating the addition of sales selected according to a single criterion, such as a salesperson’s name.
Exhibit 1 contains sales transactions for four salespeople, one of whom is Alice. To calculate her total sales, we use the formula in cell E3: SUMIF(A3:A15, D3, B3:B15), which correctly reports that Alice’s three sales ($100 + 300 + 350) add up to $750.
As you can see, SUMIF requires three pieces of data. The first is the list of criteria to check for the desired value (that is, sales by Alice). In this example, the salesperson for each transaction is listed in cells A3 through A15. That range is the first element in our SUMIF formula.
Second, SUMIF needs the selection criterion to apply when searching the range specified in the formula’s first element. Because we want to know the sum of Alice’s sales, we instruct SUMIF to search for Alice’s name—the contents of cell D3. That cell’s address is the second element in the SUMIF formula.
Finally, we specify which values to sum when the criterion in the formula’s second element is satisfied (that is, when the contents of any cell in the range A3 through A15 equal the contents of cell D3). For rows meeting that condition, SUMIF will total the related sales amounts in the range B3 through B15. That range is the final element in our SUMIF formula.
Using this formula, Excel summed in cell E3 all of Alice’s sales. To calculate sales for Jim, Samantha and Tom, insert similar formulas in cells E4, E5 and E6, respectively.
DOUBLE-BARRELED CRITERIA
Now let’s consider a harder case. Like the first one, it requires painstaking attention to detail. But this time, the process is more complicated. Instead of having to report only total sales for each salesperson, you have to calculate their sales for each month covered in the data you downloaded.
SUMIF can’t help you now; all it can handle is one criterion. You could make each salesperson’s name that criterion. But you also have to sort and add by sale date, and SUMIF’s three elements (salesperson for each transaction, individual salesperson, and sale amount) would be used up, leaving SUMIF incapable of evaluating sale date. What should you do instead?
Here’s where an apparently ill-suited Excel function, SUMPRODUCT, can help. At first, it may not seem like an ideal fit. SUMPRODUCT’s syntax [SUMPRODUCT(array1,array2,array3…)] is designed to multiply corresponding components in specified arrays, and then calculate the sum of those products. A powerful function, SUMPRODUCT can accommodate up to 30 arrays.
But why is SUMPRODUCT more useful in this case than SUMIF? To answer that question, we need to know more about how SUMPRODUCT works.
Take a look at Exhibit 2. Cells X1 to X3 contain the values 1, 2 and 3. Cells Y1 to Y3 contain 4, 5 and 6. Cells Z1 to Z3 contain 7, 8 and 9. A three-array formula would have this syntax: SUMPRODUCT(X1:X3,Y1:Y3,Z1:Z3), and it would return the value 270. The formula would arrive at this result first by multiplying and second by adding.
Specifically, it would multiply cell X1 (1) by Y1 (4) by Z1 (7) to get 28. It also would multiply row 2 (2 x 5 x 8) to get 80, and row 3 (3 x 6 x 9) to get 162. Then it would add these products (28 x 80 x 162) to get 270.
Let’s see how SUMPRODUCT functionality can accommodate our additional criterion, sale date. Exhibit 3 includes the sales data we used in Exhibit 1, as well as a column that shows the date of each sale.
SUMPRODUCT can report the totals by salesperson by month, as shown in columns F, G and H in Exhibit 3. In addition, it can do so with virtually the same formula in each of the 12 cells that contain a salesperson’s monthly total. To achieve this, we have to employ techniques that aren’t commonly used.
Examine the formula in cell H4, which totals the sales that Alice made in March: =SUMPRODUCT(--($E4=$A$3:$A$15),--(3=MONTH($B$3:$B$15)),($C$3:$C$15)). Notice that this formula contains elements that aren’t in the plain-vanilla SUMPRODUCT syntax shown above.
The first two elements in this formula contain conditions in addition to ranges. E4 (Alice) is a condition to apply to range A3:A15 (salesperson for each transaction), and 3=MONTH (March) is a condition to apply to range B3:B15 (sale date of each transaction). When these two criteria are met, the sale amount in element three, range C3:C15 (sale amount for each transaction), is returned. At the end of the operation, as explained above, SUMPRODUCT sums the products of the rows and reports that number as the result in cell H4—Alice’s total sales in March.
Let’s take a look at what SUMPRODUCT is doing here. We’ve given the formula three elements, but as you’ll note, we added something to the beginning of the first two—double dashes or minus signs. Not well-documented in Excel, double dashes will take the result of a TRUE-FALSE argument and make it a one (1) if TRUE or a zero (0) if FALSE (see sidebar, “Dissecting Double Dashes,” below). This has great implications when you use SUMPRODUCT as a conditional summing tool. In our example formula, the first element is a TRUE-FALSE question. We’ve asked Excel whether Alice’s name matches any in the list. When it goes through the list, Excel wants to tell us that some of the names do match. But with our insertion of double dashes, instead of a list of TRUE-FALSE values, we get ones and zeros, as shown in Exhibit 4.
The second element of our SUMPRODUCT formula poses a similar TRUE-FALSE question. It asks Excel to look at the date, and determine its month. The MONTH() formula will return the number corresponding to the month of a date. In this case, we’re checking to see whether the month is equal to 3, or March. Normally, we’d get a list of TRUE-FALSE arguments; but with our double dashes, once again we get a list of ones and zeros as shown in Exhibit 4.
The final element of our SUMPRODUCT formula is the list of sales figures. Since this is not a TRUE-FALSE comparison and doesn’t contain double dashes, Excel will use the sales figures themselves when it executes the SUMPRODUCT formula.
Since multiplication by zero equals zero, whenever the name doesn’t match Alice’s name, or the month isn’t March, the formula’s result is zero. When Alice’s name matches and the month is March, Excel’s result is the sales amount for that transaction. Added together, these are Alice’s sales for March—a conditional summing using more than one condition. Using absolute cell references, you easily can copy this formula into adjacent cells to quickly produce a table that lists sales by person and month.
Once you’ve familiarized yourself with this variation on SUMPRODUCT, you’ll be able to experiment with its full capacity of up to 30 arrays and tackle even more complex analyses.
J.D. Kern , CPA, is the corporate controller at Management Dynamics in East Rutherford, N.J. His e-mail address is jdkern@managementdynamics.com.
Editor's note: Click here to download this article's sample Excel worksheets. Each of the article's four exhibits is an individual worksheet in a single downloadable Excel file.
Dissecting Double Dashes
Let’s take a closer look at the double dashes preceding each of the first two elements in the formula in cell H4 of Exhibit 3. These operators are not inherently related to SUMPRODUCT, but they shape its output to better meet your reporting needs.
When Excel reads a SUMPRODUCT formula, it interprets nonnumeric values as zeros. When SUMPRODUCT compares an individual salesperson’s name to names in the sales transactions, it stores in memory as an intermediate result the value TRUE or the value FALSE, depending on the result of each comparison. Treating these nonnumeric values as zeros causes the final sum to be zero as well. But the insertion of double dashes in the formula in cell H4 returns the value $650, which is the sum of Alice’s two sales in March ($300 + 350).
Here’s why the double dashes didn’t result in a total of zero, and did come up with the right answer. Although the intermediate result of each SUMPRODUCT comparison is TRUE or FALSE, you can make Excel convert those nonnumeric results to numeric values. Because Excel will express the result of a mathematical operation as a number, you can use a simple mathematical process—negation—to convert a TRUE response into a negative one (−1) and to convert a FALSE response into a zero (0). That explains the first dash. The second dash converts the negative ones (−1) into positive ones (1), and it leaves the zeros unchanged. (Neither single nor double plus signs would do the trick.)
If you used the formula in cell H4 without the double dashes, you’d get TRUE values for the name comparisons (E4=A3:A15) in cells A4, A10 and A11, the transaction records containing Alice’s name. All the other cells in that range would return FALSE values. But if you added one dash, you’d get minus one (−1) in the three cells and zero (0) in the other ten. And if you added the second dash, the three cells would contain positive ones (1), and the others would contain zeros.
Following this logic, the second element of the formula in cell H4 would (with double dashes) return zeros (representing FALSE) in cells B3:B8, which describe January and February transactions. It would return positive ones (1, representing TRUE) in cells B9:B15, which describe March transactions.
We’re interested only in rows that have a 1 in column A and a 1 in column B (see Exhibit 4). Only two rows satisfy these conditions: rows 10 and 11, which contain A10=Alice, A11=Alice, B10=3/17/2009, and B11=3/18/2009.
In standard SUMPRODUCT fashion, the formula returns a zero value for every row that contains at least one zero—anything multiplied by zero equals zero. For rows with 1’s in columns A and B, the formula multiplies the corresponding column C value by one. A10 (1) x B10 (1) x C10 ($300) = $300; A11 (1) x B11 (1) x C11 ($350) = $350. Then, after finishing the multiplication, it sums the products ($300 + 350) and accurately returns $650 in cell H4.
Thus, the double dashes have proven their value as useful SUMPRODUCT enhancements. Exhibit 3 illustrates these results.
When you copy the H4 formula (=SUMPRODUCT(--($E4=$A$3:$A$15),--(3=MONTH($B$3:$B$15)),($C$3:$C$15)) to other cells, you’ll have to vary it slightly to accurately calculate each person’s sales for each month. Note that in cell H4’s formula, nearly every address is absolute (containing one $ for the column and another $ for the row). One address ($E4) has an absolute column coordinate ($) and a relative row coordinate (no $) to accommodate the change in salesperson name when the row 4 formula is copied down to rows 5, 6 and 7. The column coordinate in this address remains absolute because the salesperson name criterion is always in column E. For example, the formula in cell G5 for Jim’s total February sales would be =SUMPRODUCT(--($E5=$A$3:$A$15),--(2=MONTH($B$3:$B$15)),($C$3:$C$15)).
