Filtering PivotTable data in Google Sheets - Cell-by-Cell Formula Trace

Sample Data

Sales data by Region and Product with sales numbers.

Cell	Value
A1	Region
B1	Product
C1	Sales
A2	North
B2	Apples
C2	100
A3	South
B3	Oranges
C3	150
A4	East
B4	Bananas
C4	200
A5	North
B5	Oranges
C5	120
A6	South
B6	Apples
C6	130
A7	East
B7	Apples
C7	90

Formula Trace

=QUERY(A1:C7, "select A, sum(C) where B = 'Apples' group by A", 1)

Step 1: QUERY(A1:C7, "select A, sum(C) where B = 'Apples' group by A", 1)

Step 2: Filter rows where B = 'Apples'

Step 3: Group filtered rows by A (Region) and sum C (Sales)

Step 4: Add header row from original data

Step 5: Final output

Cell Reference Map

    A       B        C
1 |Region | Product | Sales
2 | North | Apples  | 100  
3 | South | Oranges | 150  
4 | East  | Bananas | 200  
5 | North | Oranges | 120  
6 | South | Apples  | 130  
7 | East  | Apples  | 90   

Formula references A1:C7 for all data.

The QUERY formula uses the full data range A1:C7 including headers and all rows.

Result

    D       E
1 |Region | sum
2 | North | 100
3 | South | 130
4 | East  | 90

The result shows total sales of Apples grouped by Region in columns D and E.

Sheet Trace Quiz - 3 Questions

Which rows are included after filtering for 'Apples'?

A Rows 1, 4, and 7

B Rows 2, 6, and 7

C Rows 3, 5, and 6

D Rows 2, 3, and 4

Key Result

QUERY(data_range, "select columns where condition group by column", header_rows)