For business users of Microsoft Excel Free guides and templates

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, 2005-2014
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="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.

 

SUMPRODUCT Function

How to Perform Multiple Table Searches Using the SEARCH & SUMPRODUCT Functions in Excel

Use SUMPRODUCT in an Excel Table to Filter Any Number of Items

Free Excel Dashboards


Charley's SwipeFile charts