How Excel's SUMPRODUCT Formula Can Simplify Your Spreadsheets

Never thought Excel's SUMPRODUCT formula could become your go-to formula for complex Lookups?

Think again.

The SUMPRODUCT function is in fact THE function to work smart in Excel. It is an array function but is different than most of Excel’s array functions in that it doesn’t require Ctrl + Shift + Enter (CSE). It wears many hats and has many uses other than the traditional sum of products formula, which the name implies.  It can be used to conditionally SUM, conditionally COUNT and for complex lookup challenges.

Watch this video to find out more about the SUMPRODUCT function. You can get all of it in writing HERE. Download the practice workbook on the page to follow along.

Think you got it? Download the Exercise book from this page (scroll down to the bottom) to test your knowledge.

Here's a Quick Summary

First off let's demonstrate the classical use of SUMPRODUCT. 

This function can be written in two ways which returns the same result. Using either Excel separator “,” (comma) or using the multiply sign “*”. However, when SUMPRODUCT is used in non-classical ways, it is generally necessary to use “*” operator instead of the “,”. I will explain why in a bit.

The side of SUMPRODUCT most people don't know, is it can check for exceptions. You just have to get your brackets rights. The formula becomes:

=SUMPRODUCT(([array1]=condition)*[array2])

Notice, I've replaced the "," with a "*". That's the tricky part: You can't use the suggested "," separator anymore to separate the arguments. If you write this:

=SUMPRODUCT(([array1]=condition),[array2])

You get zero. But if you write this:

=SUMPRODUCT(--([array1]=condition),[array2])

it works. Why?

The double negatives and the multiplication sign have the purpose of forcing Excel to switch Boolean values to 1 and 0 values. Where are the Boolean values? They get generated by this part of the formula:

=SUMPRODUCT(([array1]=condition)...)

This check results in an array of True and False values. The only way to translate True to 1 and False to 0 is by performing some sort of mathematical operation on the array. That's what the double negatives or the multiplication achieve.

More Than Meets the Eye

One of the advantages that SUMPRODUCT has over SUMIFS or COUNTIFS is that it can understand OR conditions besides checking for AND conditions. OR conditions are written in SUMPRODUCT using the “+” operator. AND conditions are denoted using the “*” operator. Using parenthesis to group the AND / OR criteria is crucial for the SUMPRODUCT function to interpret the different criteria groupings properly.

For more examples read this article and download the free workbook.