One cell away
| A | B | |
|---|---|---|
| 1 | Item | Sales |
| 2 | Pens | 40 |
| 3 | Ink | 90 |
| 4 | Pads | 25 |
Formula: D2: =OFFSET(A1,2,1)
Result: 90
Two rows down and one column right of A1 lands on B3.
Google Sheets function
OFFSET in Google Sheets
OFFSET with worked examples you can test in the live grid. The argument details are in the Excel guide.
In Google Sheets
The arguments and common errors are covered in the OFFSET Excel guide; check the function help in Google Sheets for its own argument list.
Worked examples in Google Sheets
A window of three cells
| A | B | |
|---|---|---|
| 1 | Month | Sales |
| 2 | Jan | 10 |
| 3 | Feb | 20 |
| 4 | Mar | 30 |
| 5 | Apr | 40 |
Formula: D2: =SUM(OFFSET(B1,1,0,3,1))
Result: 60
The range starts one row below B1 and is three rows high, so it covers B2:B4.
The latest three entries
| A | B | |
|---|---|---|
| 1 | Month | Sales |
| 2 | Jan | 10 |
| 3 | Feb | 20 |
| 4 | Mar | 30 |
| 5 | Apr | 40 |
Formula: D2: =SUM(OFFSET(B1,COUNT(B2:B5)-2,0,3,1))
Result: 90
COUNT gives 4 entries; moving 2 rows down places the window on B3:B5.
Try it
A tiny spreadsheet with example data. Edit the formula or any number and see the result.
Formula in F2: =SUM(OFFSET(D2,1,0,3,1))
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | ID | Item | Price | Stock | Result | |
| 2 | 101 | Pen | 2.5 | 120 | 135 | |
| 3 | 102 | Notebook | 4 | 80 | ||
| 4 | 103 | Lamp | 25 | 15 | ||
| 5 | 104 | Mug | 7.5 | 40 | ||
| 6 | 105 | Backpack | 39 | 12 |
Result in F2: 135
Google Sheets is used here only as a descriptive product name. FormulaCamp is not affiliated with Google.