SUMPRODUCT and SUMIFS are Excel’s two most powerful functions for returning filtered data from a table. SUMPRODUCT is more powerful, but SUMIFS is faster.
Unfortunately, Microsoft’s documentation about SUMPRODUCT lacks critical information: SUMPRODUCT actually has two syntax methods, not just the one method that Microsoft describes.
SUMPRODUCT multiplies arrays and then sums the result. With Method 1, which is the Microsoft approach, each array is placed in a separate SUMPRODUCT argument. But with Method 2—the method I always use—you multiply each array explicitly within the first argument. To illustrate:
Method 1: SUMPRODUCT(array1, [array2],…)
Method 2: SUMPRODUCT(array1*[array2]*…)
To see the problem with Method 1, suppose one of the arrays is generated by a test like: SKUs=”A43-1″. This test produces an array of TRUE values wherever an item in the SKUs column equals “A43-1″, and FALSE values otherwise. But Excel can’t multiply those TRUE and FALSE values using Method 1. Instead you must convert them to 1 or 0 values by adding a zero, multiplying by 1, or adding two minus signs. If you wanted the sales for all products with a SKU of A43-1, you could use either of these formulas under Method 1:
=SUMPRODUCT((SKUs=”A43-1″)+0,Sales)
=SUMPRODUCT((SKUs=”A43-1″)*1,Sales)
=SUMPRODUCT(–(SKUs=”A43-1″),Sales)
On the other hand, using Method 2 automatically converts TRUE and FALSE to 1 and 0, like this:
=SUMPRODUCT((SKUs=”A43-1”)*Sales)
If you haven’t discovered SUMPRODUCT’s Method 2 until now, you might give it a try.
