Named ranges in Google Sheets - Cell-by-Cell Formula Trace

Sample Data

This sheet lists products and their prices in columns A and B. Column D shows quantities bought. The named range 'Prices' refers to B2:B4.

Cell	Value
A1	Product
A2	Apple
A3	Banana
A4	Cherry
B1	Price
B2	1.2
B3	0.5
B4	2.0
D1	Quantity
D2	3
D3	5
D4	2

Formula Trace

=SUMPRODUCT(Prices, D2:D4)

Step 1: Prices

Step 2: D2:D4

Step 3: SUMPRODUCT([1.2, 0.5, 2.0], [3, 5, 2])

Cell Reference Map

    A       B       C       D
1 |Product| Price |       |Quantity
2 | Apple |  1.2  |       |   3    
3 |Banana |  0.5  |       |   5    
4 |Cherry |  2.0  |       |   2    

Named Range 'Prices' -> B2:B4
Formula references 'Prices' and D2:D4

The formula uses the named range 'Prices' which points to B2:B4 and the range D2:D4 for quantities.

Result

    A       B       C       D       E
1 |Product| Price |       |Quantity| 
2 | Apple |  1.2  |       |   3    | 
3 |Banana |  0.5  |       |   5    | 
4 |Cherry |  2.0  |       |   2    | 
5 |       |       |       |        | 10.1

Cell E5 shows the formula result: 10.1

The total cost calculated by the formula =SUMPRODUCT(Prices, D2:D4) is 10.1, shown in cell E5.

Sheet Trace Quiz - 3 Questions

What cells does the named range 'Prices' refer to?

A A2:A4

B D2:D4

C B2:B4

D B1:B3

Key Result

Named ranges can be used in formulas like ranges; here SUMPRODUCT multiplies corresponding elements and sums them.