OFFSET for dynamic ranges in Excel - Cell-by-Cell Formula Trace

Sample Data

Sales data for different fruits in column B. Cell D2 contains the number of items to include dynamically.

Cell	Value
A1	Item
B1	Sales
A2	Apples
B2	50
A3	Bananas
B3	30
A4	Cherries
B4	20
A5	Dates
B5	40
A6	Elderberries
B6	10
D1	Count
D2	4

Formula Trace

=SUM(OFFSET(B2,0,0,D2,1))

Step 1: OFFSET(B2, 0, 0, D2, 1)

Step 2: SUM(B2:B5)

Step 3: Final result

Cell Reference Map

    A       B       C       D
1 | Item  | Sales |       | Count
2 | Apples|  50   |       |  4  
3 | Bananas| 30   |       |     
4 | Cherries|20   |       |     
5 | Dates |  40   |       |     
6 | Elderberries|10|       |     

OFFSET starts at B2 and uses D2 for height.

The formula uses cell B2 as the starting point and cell D2 to determine how many rows to include in the range.

Result

    A       B       C       D
1 | Item  | Sales |       | Count
2 | Apples|  50   |       |  4  
3 | Bananas| 30   |       |     
4 | Cherries|20   |       |     
5 | Dates |  40   |       |     
6 | Elderberries|10|       |     

Result in any cell with formula: 140

The formula sums the first 4 sales values (50+30+20+40) and returns 140.

Sheet Trace Quiz - 3 Questions

What does the OFFSET function return in this formula?

A A range from B2 down 4 rows and 1 column wide

B A single cell B2

C A range from B2 to B6

D A range from A2 to A5

Key Result

OFFSET(start_cell, rows, cols, height, width) returns a dynamic range starting at start_cell offset by rows and cols, with given height and width.