Treemap and sunburst in Excel - Cell-by-Cell Formula Trace

Sample Data

Sales data by category and subcategory used to create treemap and sunburst charts.

Cell	Value
A1	Category
B1	Subcategory
C1	Sales
A2	Fruits
B2	Apples
C2	100
A3	Fruits
B3	Oranges
C3	150
A4	Vegetables
B4	Carrots
C4	200
A5	Vegetables
B5	Broccoli
C5	120

Formula Trace

=SUMIFS(C2:C5, A2:A5, "Fruits")

Step 1: SUMIFS(C2:C5, A2:A5, "Fruits")

Cell Reference Map

    A       B       C
1 Category Subcat  Sales
2 Fruits   Apples   100
3 Fruits   Oranges  150
4 Vegetables Carrots  200
5 Vegetables Broccoli 120

Formula references:
- C2:C5 (Sales values)
- A2:A5 (Category names)

The formula uses the Sales column (C2:C5) and filters by Category column (A2:A5) to sum only 'Fruits' sales.

Result

    A       B       C       D
1 Category Subcat  Sales   Total Fruits
2 Fruits   Apples   100       250
3 Fruits   Oranges  150
4 Vegetables Carrots  200
5 Vegetables Broccoli 120

The formula result 250 is shown in cell D2 as the total sales for the 'Fruits' category, which can be used to build treemap or sunburst charts.

Sheet Trace Quiz - 3 Questions

What does the formula =SUMIFS(C2:C5, A2:A5, "Fruits") calculate?

A Sum of sales where category is Fruits

B Sum of all sales

C Sum of sales where subcategory is Apples

D Count of Fruits entries

Key Result

SUMIFS sums values in one range based on matching criteria in another range.