## 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:

### 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 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!

## About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
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

This site uses Akismet to reduce spam. Learn how your comment data is processed.