Formula Power
The SUMPRODUCT Function's Undocumented Method
Excel's SUMPRODUCT Function offers more power and flexibility
than its documentation describes.
by Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

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="A431". This test produces an
array of TRUE values wherever an item in the SKUs column equals
"A431", 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 A431, you could use either of these
formulas under Method 1:
=SUMPRODUCT((SKUs="A431")+0,Sales)
=SUMPRODUCT((SKUs="A431")*1,Sales)
=SUMPRODUCT((SKUs="A431"),Sales)
On the other hand, using Method 2 automatically converts TRUE
and FALSE to 1 and 0, like this:
=SUMPRODUCT((SKUs="A431")*Sales)
If you haven't discovered SUMPRODUCT's Method 2 until now, you
might give it a try.
