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

By Ali, Founder, Excel Lessons

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.

Read more about this

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

SUMPRODUCT multiplies ranges row by row, then adds them.

In E2, type:

=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

    SUMPRODUCT multiplies ranges row by row, then adds them. In E2, type: =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, find the weighted average price with SUMPRODUCT and SUM. Shape: =SUMPRODUCT(range,range)/SUM(range). That is value divided by quantity.

    Hint. Weighted total over total weight.

  4. 4

    A comparison in brackets acts as 1 or 0 inside SUMPRODUCT. In E6, find the North lines' value with SUMPRODUCT. Shape: =SUMPRODUCT((range="text")*range*range).

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

  5. 5

    In E8, count the lines over 100 with SUMPRODUCT. Shape: =SUMPRODUCT((range>value)*1).

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