SUM the PRODUCTs or SUMPRODUCT? That’s the question.

The basic of SUMPRODUCT

Excel Tips - SUMPRODUCT basic 1

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.

Excel Tips - SUMPRODUCT basic 2

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)

Excel Tips - SUMPRODUCT basic 3

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

Excel Tips - SUMPRODUCT basic 4

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

Excel Tips - SUMPRODUCT basic 4.1

 

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

Excel Tips - SUMPRODUCT basic 5(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:Excel Tips - SUMPRODUCT basic 5.1

SUM the PRODUCTs please. 🙂

As simple as this!

More examples

Example1 – SUMPRODUCT with horizontal arraysExcel Tips - SUMPRODUCT basic 7

Of course… but still need to mention. 🙂

Example 2 – SUMPRODUCT with three 1D arrays

Excel Tips - SUMPRODUCT basic 6Do you really need the helper range E2:F5 to get the “Total Profit”?

Example 3 – SUMPRODUCT with two 2D arraysExcel Tips - SUMPRODUCT basic 8

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

Excel Tips - SUMPRODUCT basic (Error1)

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

Excel Tips - SUMPRODUCT basic (Error2)

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

Excel Tips - SUMPRODUCT basic

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!

Advertisements
This entry was posted in Formula and tagged . Bookmark the permalink.

2 Responses to SUM the PRODUCTs or SUMPRODUCT? That’s the question.

  1. MF says:

    Thanks Puneet! That’s a good one.
    Indeed i will dive into more details about SUMPRODUCT next next post. 😁

    Like

  2. Puneet Gogia says:

    I love you illustrations. But, SUMPRODUCT is a kind of thing which I love the most. One of the reason is here ==> http://excelchamps.com/blog/condition-in-sumproduct/

    Like

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s