Problem: Before Excel introduced SUMIFS in Excel 2007, you would have to use the SUMPRODUCT function to solve problems that can be solved by SUMIFS.
Strategy: Once you get the hang of it, SUMPRODUCT is cool.
Say that you want to calculate the sales of Green XL EasyXL shirts. This figure uses 37 formulas to calculate the $6800.
- 9 rows, 3 conditions, 37 formulas.
The SUMPRODUCT function replaces those 37 formulas with a single formula. Here is the basic structure:
=SUMPRODUCT((Criteria1)*(Criteria2)*(Criteria3),(Numbers))
I am guessing that SUMPRODUCT was added to Excel to do matrix multiplication. Multiply one rectangular range by another rectangular range and sum the products.
Before Excel 2007 came along, Excel tricksters started using SUMPRODUCT to do SUMIFS before SUMIFS was invented.
In essence, you are telling Excel to multiply four arrays of values.
- The first array is (A4:A12="Green"). This evaluates to the array of TRUE/FALSE values shown in F4:F12.
- The next array is (B4:B12="XL"). Notice that you always put these logical tests in parentheses in order to force Excel to evaluate them first. This array results in the values shown in G4:G12.
- The next array is (C4:C12="EasyXL"). It evaluates to the values in H4:H12.
- The last array is the numbers in D4:D12.
Flip back to Fig 399 to read what happens when you multiply a number by TRUE or FALSE. A number times TRUE is the number. A number times FALSE is 0.
When you understand that TRUE is like 1 and FALSE is like 0, you can see that the only amounts which make it through to column J are those where each of the conditions is TRUE.
- Any FALSE becomes a 0, making the product for that row 0.
Additional Details: Calculations in the grid are handled a bit differently than calculations that use functions. 10*TRUE*TRUE in the grid comes out as 10. However, =SUMPRODUCT(10,TRUE,TRUE) will not work. The SUMPRODUCT program refuses to convert the TRUE values to 1's on the fly. I don't know if this is a bug, or if it is simply a matter of the original intention of SUMPRODUCT was to multiply matrices of numbers.
So, according to the syntax in Excel Help, one might think that this formula would work:
=SUMPRODUCT(A4:A12="Green",B4:B12="XL",C4:C12="EasyXL",D4:D12)
It does not. There are workarounds, and several really smart people disagree on which workaround is the best. We all agree that you have to do some mathematical operation on the logical arrays to coerce them to change from TRUE/FALSE to 1/0. Several people do this in a two-step process. They use a unary minus to change the TRUE to -1 and the FALSE to 0. They then have to use a secondary unary minus to convert the -1 back to 1. Their formula might look like this:
=SUMPRODUCT(--(A4:A12="Green"),--(B4:B12="XL"),--(C4:C12="EasyXL"),D4:D12)
There are downsides to this formula. Excel has to touch each logical formula twice using this method, once for each unary minus. Also, for a brief time, Excel 2007 was letting the AutoCorrect change the minus minus to an em-dash. Brilliant in Word, not at all useful in an Excel formula.
When I do a SUMPRODUCT, I don't use the double unary minus. Instead, I multiply all of the logical arrays together. This calculation is like doing a calculation in the grid, so it automatically converts TRUE/FALSE to 1/0.
=SUMPRODUCT((A4:A12="Green")*(B4:B12="XL")*(C4:C12="EasyXL")*(D4:D12))
The detractors of this method say that Excel Help indicate that the arrays should be separated by commas, not by parentheses. They say that using this method you are doing all of the work in Excel and you are not letting SUMPRODUCT do any multiplying. By having Excel do the work, the only thing left to do is to SUM the results of the products done by Excel. I don't buy this argument. The fact is that something has to do the multiplication, and I don't care if it is the Excel calculation engine or the SUMPRODUCT function. If I am reducing SUMPRODUCT to a SUM function, so be it.
If you really want to let SUMPRODUCT do some multiplying, then use a hybrid of the two approaches:
=SUMPRODUCT((A4:A12="Green")*(B4:B12="XL")*(C4:C12="EasyXL"),D4:D12)
- One of three ways to do a SUMPRODUCT.
Gotcha: While SUMPRODUCT is powerful, the built-in SUMIFS will calculate sometimes 1000 times faster than SUMPRODUCT. If everyone using your worksheet is using Excel 2007 or newer, use SUMIFS instead.
Gotcha: Up through Excel 2007, there was an obscure add-in called the Conditional Sum Wizard. This add-in would not use SUMPRODUCT or SUMIFS. It would build a formula such as {=SUM(IF(A4:A12="Green",IF(B4:B12="XL",IF(C4:C12="EasyXL",D4:D12,0),0),0))}. Anytime you see the curly braces around the formula, it is a super-secret type of formula that Microsoft calls an array formula. If you attempt to edit this formula, you have to finish the formula by holding down Ctrl+Shift while pressing Enter.