Subtotals in Excel - Cell-by-Cell Formula Trace

Sample Data

This data shows categories of items and their amounts. We want to calculate subtotals for each category.

Cell	Value
A1	Category
B1	Amount
A2	Fruits
B2	10
A3	Fruits
B3	15
A4	Vegetables
B4	20
A5	Vegetables
B5	25
A6	Grains
B6	30

Formula Trace

=SUBTOTAL(9, B2:B6)

Step 1: SUBTOTAL(9, B2:B6)

Cell Reference Map

    A         B
1 Category  Amount
2 Fruits    10  
3 Fruits    15  
4 Vegetables 20  
5 Vegetables 25  
6 Grains    30  

Formula references cells B2 to B6 for amounts.

The formula uses the range B2:B6 which contains the amounts to sum.

Result

    A         B
1 Category  Amount
2 Fruits    10  
3 Fruits    15  
4 Vegetables 20  
5 Vegetables 25  
6 Grains    30  
7           100 

Cell B7 shows the subtotal sum of amounts 100.

The subtotal formula result 100 is shown in cell B7, summing all amounts in B2:B6.

Sheet Trace Quiz - 3 Questions

What does the number 9 mean in the SUBTOTAL formula?

A It tells SUBTOTAL to find average

B It tells SUBTOTAL to sum the numbers

C It tells SUBTOTAL to count numbers

D It tells SUBTOTAL to find maximum

Key Result

SUBTOTAL(function_num, range) calculates a summary like SUM or AVERAGE on the range, ignoring hidden rows if any.