more-arw search

How To Use A Secret Excel SUMPRODUCT Method

When Microsoft introduced Excel 2007, one of its best improvements was the Excel Table feature. This allows you to transform worksheets with columnar data into a simple database that PivotTables and worksheet formulas can reference easily.

When you use worksheet formulas to retrieve data from an Excel Table, you’ll probably use SUMPRODUCT or SUMIFS. SUMPRODUCT is more powerful, but SUMIFS is faster.

You can find detailed documentation about SUMPRODUCT and SUMIFS in the Excel Help area of my web site. My SUMPRODUCT page includes information that Microsoft’s help page about the topic lacks: SUMPRODUCT actually has two syntax methods, not just the one method that Microsoft describes.

SUMPRODUCT multiplies one or more arrays, row-by-row, in memory and then adds up all those results. With the Microsoft approach, Method 1, each array is placed in a separate SUMPRODUCT argument. But with the method I always use, Method 2, the arrays are explicitly multiplied within the first argument. That is:

Method 1: SUMPRODUCT(array1, [array2],…)

Method 2: SUMPRODUCT(array1*[array2]*…)

Method 2 works around a problem that’s common with Method 1. Specifically, when you use SUMPRODUCT with Method 1, you typically use it somewhat like this:

=SUMPRODUCT(test1, test2, test3, data)

That is, test1 might limit the data to Region 5; test2 might further limit the data to January, 2013; and test3 might limit it even further to a particular Stocking Keeping Unit (SKU). Finally, because all other data has been multiplied by 0 in at least one of the three tests, SUMPRODUCT returns the sum of all non-zero data, which is the data on the only rows that satisfy all three criteria.

Drilling down somewhat, suppose the test2 array is generated by a test like: SKUs=“S1235″. This test produces an array of TRUE values in memory wherever an item in the SKUs column equals “S1235″, and FALSE values otherwise. But Excel can’t multiply those TRUE and FALSE values using Method 1. Instead you first must convert them to 1 or 0 values by adding a zero to the TRUE or FALSE results, or multiplying by 1.

To illustrate, if you wanted the sales for all products with a SKU of S1235, Method 1 offers you two choices for the formula:

=SUMPRODUCT((SKUs=“S1235″)+0,Sales)

=SUMPRODUCT((SKUs=“S1235″)*1,Sales)

On the other hand, with Method 2, Excel automatically converts TRUE and FALSE to 1 and 0, like this:

=SUMPRODUCT((SKUs=“S1235″)*Sales)

And finally, if you were to add the other two filters for Method 2, your formula might look something like this:

=SUMPRODUCT((Regions=“Region 5”)*(TEXT(Dates,”mm-yyyy”)=“01-2013”)*(SKUs=“S1235″)*Sales)

(Another way to limit the selection to a particular month is to test that the month and year of each date match the month and year of the cell with the current period’s date. Test both alternatives and use the version that calculates more quickly, if you see a significant difference between them.)

If you haven’t discovered the SUMPRODUCT worksheet function until now, or used its Method 2, you might want to give it a try.

 

Products and Companies: