Subscriber only lesson.
Sign up to this course to view this lesson.
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.
- 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:
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:
This formula is picking up ‘Standard’ costs rather than the ‘Budget’ costs
Lesson notes are only available for subscribers.