Quick Navigation

Not every function has to be complicated in order to save time and clean up cluttered worksheets. **SUMPRODUCT** is a basic function that combines the uses of **PRODUCT** and **SUM** (brush up on the **SUM** formula here) to save space and improve readability of your models and dashboards. This quick tutorial will show you how it works…

## SUMPRODUCT Basics

The syntax of **SUMPRODUCT** is as follows:

=SUMPRODUCT(array1,[array2],…)

It takes any number of arrays, multiplies the corresponding cells, and then adds up the outcomes. The arrays have to be the same size for the math to work. Take a look at this quick example to see how **SUMPRODUCT** works, compared with the basic **PRODUCT** and **SUM** functions.

## SUMPRODUCT Example

[iframe width=”620″ height=”400″ frameborder=”0″ scrolling=”no” src=”https://skydrive.live.com/embed?cid=56D8503869383D0E&resid=56D8503869383D0E%21120&authkey=ALNKKqCBHC8WZok&em=2&AllowTyping=True&wdDownloadButton=True”]

In the example above, Array 1 (**B2:C6**) and Array 2 (**E2:F6**) are just sets of numbers. Using the PRODUCT function, we can multiply the cells together individually and it would look like the PRODUCT table (**H2:I6**). An example of the formula is in cell **I2**:

=PRODUCT(C2,F2)

It multiplies 6 by 16 and calculates to 96.

To get the total of these products, we have to sum them. We can sum the columns, as in **H9** and **H10**:

=SUM(H2:H6)

=SUM(I2:I6)

Or we can sum the entire array, as in **H11**:

=SUM(H2:I6)

**SUMPRODUCT** lets us save time, economize on space, and get the same result. **SUMPRODUCT** can multiply two columns together and add them up, as in **H12** and **I12**:

=SUMPRODUCT(B2:B6,E2:E6)

=SUMPRODUCT(C2:C6,F2:F6)

Finally, with its crowning achievement, **SUMPRODUCT** can multiply two arrays together pair-wise, and then add the results, as in cell **H14**:

=SUMPRODUCT(B2:C6,E2:F6)

By doing this, **SUMPRODUCT** can immediately get you the answer you want without all the clutter of the PRODUCT table and the separate SUM formulas. In a crowded spreadsheet, it’s a lifesaver, and you’ll find yourself using it again and again. Check out other functions in the **SUM** family, including the **SUM** function and **SUMIF **function!

SUMPRODUCT very well explained !

However, if you could kindly let me know as to how to get SUM of (SUMPRODUCTs of multiple rows with a given row) ?