Basic Functions
Intermediate

SUMPRODUCT for an order total

Quantity times price for every line, added up, in one cell.

Task:

The order form has quantities and unit prices but no line totals. In B9 give the order total without adding a column of line totals.

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

Variations of SUMPRODUCT

0/3

The same formula in the other shapes it takes at work.

  1. SUMPRODUCT Function

    Calculate weighted sums using SUMPRODUCT.

    Core
    Advanced
  2. SUMPRODUCT for an order total

    Quantity times price for every line, added up, in one cell.

    Intermediate
  3. A weighted average with SUMPRODUCT

    Each value counts in proportion to its weight.

    Advanced

The data in this exercise

This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.

9 rows × 3 columns1 cell you fill in
ABC
1ItemQtyUnit price
2A4 paper (box)621.5
3Toner264
4Lever-arch file152.4
5Desk tidy38.75
6Whiteboard pens101.2
7
8
9Order total
What this exercise teachesMay contain the answer

A helper column of line totals works, but it is one more column to keep in step and one more place for a typed-over number to hide. SUMPRODUCT does the row-by-row multiplication internally and returns only the total.