## About this lesson

An advanced example of the use of SUMPRODUCT.

## Exercise files

Download this lessonâ€™s related exercise files.

SUMPRODUCT Part 2.xlsx12.6 KB SUMPRODUCT Part 2 - Solution.xlsx

12.7 KB

## Quick reference

### SUMPRODUCT Part 2

Discover how to use SUMPRODUCT function in a formula.

### When to use

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

### Instructions

#### Example

- To find the ‘Total Costs’ in the above example, using the SUMPRODUCT function, the formula is as follows

=**SUMPRODUCT($E$6:$E$9*$F$6:$K$9*$F$14:$K$14)** (which is $612,660)

- To find the ‘Budget’ costs you can add *
**($D$6:$D$9=G$18))**on to the formula shown above, which gives:

**=SUMPRODUCT($E$6:$E$9*$F$6:$K$9*$F$14:$K$14*($D$6:$D$9=G$18))**

The last part of the formula is saying: only include the figures when the ‘Type’ shown in cells D6:D9 match cell G18 (Budget)

- To find the ‘Standard’ costs you can copy the formula across from G19. The formula for that cell is:

**=SUMPRODUCT($E$6:$E$9*$F$6:$K$9*$F$14:$K$14*($D$6:$D$9=H$18))**

This formula is picking up ‘Standard’ costs rather than the ‘Budget’ costs

Lesson notes are only available for subscribers.