The basic of SUMPRODUCT

Suppose we have a column showing retail price of different items; another column showing the units sold (illustrated above).  To get the total sales, most people will deploy a helper column to get the sales of each item first, and then get the total at the end of the helper column, as shown in the following screenshot.

This makes sense especially when we are also interested in looking at the total sales by item.  However, if we are only interested in grand total, we may get the answer directly by using SUMPRODUCT.

You may download a sample file to follow through.

=SUMPRODUCT(B2:B5,C2:C5)

The Syntax is simple:

=SUMPRODUCT(array 1, [array 2], [array 3]...)

• Where array 1 is required.  It can be 1D (i.e. vertical column or horizontal row), or 2D; range (reference from sheet, e.g. B2:B5) or array returned by formula; or even hard-code wrapped by {}, e.g. {1,2,3,4}.
• Array 2 to 255 are optional.

What it does?

Multiplies corresponding components in the given arrays, and returns the sum of those products.

This is the description from support.office.com.  Does it explain to you clearly?

As always, a picture tells thousand words.  Let’s evaluate SUMPRODUCT step-by-step:

First, in the formula bar, select “B2:B5” and then press F9

Repeat the step to C2:C5, you will see:

The following screenshot illustrates how the “Multiplies corresponding components” works…

(please excuse my poor handwrite… but that’s the point of using a Surface Pro4 :p)

Does the function name SUMPRODUCT make sense to you now?

Indeed, if you know the existence of a function called PRODUCT, it makes more sense to you.   A formula written as

=B2*B3

can be replaced by PRODUCT function:

=PRODUCT(B2:B3)

Now the following screenshot should tell you what SUMPRODUCT does:

SUM the PRODUCTs please. 🙂

As simple as this!

More examples

Example1 – SUMPRODUCT with horizontal arrays

Of course… but still need to mention. 🙂

Example 2 – SUMPRODUCT with three 1D arrays

Do you really need the helper range E2:F5 to get the “Total Profit”?

Example 3 – SUMPRODUCT with two 2D arrays

Well… let’s get the result directly by SUMPRODUCT if what you care is only the Total.

As mentioned in the Syntax, SUMPRODUCT takes up to 255 arrays.   Honestly, I have never feed more than 10 arrays (most of the time, 2 or 3 arrays only) into SUMPRODUCT. 

Special notes for using SUMPRODUCT

Note 1 – All non-numeric values in array will be treated as 0

Note 2 – All arrays (be it 1D or 2D) must be of equal size, else you’ll get #VALUE

Note 3 – When there is only 1 array, SUMPRODUCT behaves like SUM

This sound silly but indeed not.  SUMPRODUCT accepts array while SUM does not (without Ctrl+Shift+Enter).  This gives SUMPRODUCT huge advantage over SUM.

This is also important for understanding advanced use of SUMPRODUCT, which will be discussed later.  Stay tune!