Course outline / Mathematical functions

Lesson 47 of 75 · Intermediate · 4 exercises

Totals with SUBTOTAL

One function for sums, averages, counts and maximums that ignores other subtotals.

Start this lesson free

What you'll practise

  1. SUBTOTAL for a sum
  2. SUBTOTAL for an average
  3. SUBTOTAL for the largest value
  4. SUBTOTAL for a count

SUBTOTAL for a sum

SUBTOTAL can sum, average, count and more. The first argument is a code that says which:

9 sum, 1 average, 2 count, 4 maximum, 5 minimum.

Its big advantage is that it skips other SUBTOTAL formulas in the range, so section totals are never counted twice. With codes 101 to 111 it also skips rows you have hidden.

In cell D2, use SUBTOTAL with code 9 to add up B2:B5.

SUBTOTAL for an average

Change the code to change the calculation.

In cell D2, use SUBTOTAL with code 1 to average B2:B5.

SUBTOTAL for the largest value

In cell D2, use SUBTOTAL with code 4 to find the largest sale in B2:B5.

SUBTOTAL for a count

In cell D2, use SUBTOTAL with code 2 to count the numbers in B2:B5.

Try it in a real spreadsheet

Every step checks your answer instantly and tells you exactly what to fix.

Start the lesson

← Percentiles and quartilesWeighted averages →