Excel Practice
Lessons Lesson 94

SUMPRODUCT: Weighted Totals and Conditions

About this lesson

SUMPRODUCT multiplies ranges together row by row and adds the results. That gives an order value without a line-total column. Divided by the total weight, it gives a weighted average. With a comparison in brackets as one of the ranges, it gives a conditional sum.

By the end you can

  • work out an order value without a line-total column.
  • calculate a weighted average.
  • sum or count by a condition on a calculation.

Practises SUMPRODUCT SUM SUMIFS AVERAGE

The idea

Two ranges, paired row by row, multiplied, and added. That is the whole function, and three of the most useful formulas on any sheet come out of it. Quantities times prices is an order value. That value divided by the total quantity is a weighted average, which AVERAGE of the prices gets wrong. And a comparison in brackets, (A2:A6="North"), is a column of ones and zeros that switches rows on and off.

SUMIFS is clearer when the condition is on a column. SUMPRODUCT is the tool when the condition is on a calculation, like lines whose value is over 100. No conditional function can test an expression it was not given a column for.

The mistake to watch for

Ranges of different sizes. SUMPRODUCT pairs its ranges row by row and refuses when they do not line up. That is the loud failure. The quiet one is a plain AVERAGE of the prices where a weighted average was meant. The value of the order divided by the total quantity is the price really paid. AVERAGE of the price column is not.

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

In E2, the value of the whole order: each quantity times its price, added up, in one SUMPRODUCT.

To begin, type it exactly:

=SUMPRODUCT(C2:C6,D2:D6)

E2
Row A B C D E F G
1 Region Item Qty Price Order value
2 North Tee 12 8
3 South Hoodie 5 22 Average price paid
4 North Cap 20 12
5 East Tee 15 9 North value
6 North Hoodie 3 24
7 Lines over 100
8

Every step

  1. 1

    In E2, the value of the whole order: each quantity times its price, added up, in one SUMPRODUCT. To begin, type it exactly: =SUMPRODUCT(C2:C6,D2:D6).

    Hint. Two ranges, same size.

  2. 2

    Read =SUMPRODUCT(C2:C3,D2:D3) and say what it returns for the first two lines.

    Hint. Two products, added.

  3. 3

    In E4, the average price really paid per item. That is the order value divided by the total quantity. Use SUMPRODUCT for the value and SUM for the quantity.

    Hint. Weighted total over total weight.

  4. 4

    In E6, the value of the North lines only. Compare the region column with the text North in brackets. Multiply that by the quantities and by the prices, all inside one SUMPRODUCT.

    Hint. The comparison in brackets, then the two columns.

  5. 5

    In E8, write a formula using SUMPRODUCT that counts the lines whose quantity times price is over 100.

    Hint. (C2:C6*D2:D6>100)*1.