# The SUMPRODUCT Function’s Undocumented Method

Excel's SUMPRODUCT Function offers more power and flexibility than its help files describe. Here's a new way to use one of Excels' most-powerful functions.

3211

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.

Early in my career, I worked nearly 20 years as the CFO of turnarounds and startups. But I eventually got burned out fighting continual struggles with cash flow. That's when I started to write about Lotus 1-2-3, the spreadsheet software that I'd been using most of the time during the CFO days. When Excel was about to be introduced for the PC, one of my magazine editors set up a meeting for me to see the product, talk with the developers, and write a cover story about Excel. So I used the first version of Excel before it was launched. And I had also used the first version of VisiCalc before it was launched. And then,