Excel OFFSET Function
The Excel OFFSET function returns a reference to a cell or a range of cells that is offset from a specific cell by a given number of rows and columns.
Syntax
=OFFSET (reference, rows, cols, [height], [width])
Arguments
Reference (required): A cell or a range of adjacent cells you will set as the starting point.
Rows (required): The number of rows to move up (negative number) or down (positive number) from the starting point.
Cols (required): The number of columns to move left (negative number) or right (positive number) from the starting point.
Height (optional): The number of rows you want to return. The height must be a positive number.
Width (optional): The number of columns you want to return. The width must be a positive number.
Return value
The OFFSET function will return a cell reference offset from a given starting point.
Function notes
1. #VALUE! error value will return when the reference refer to a range of discontinuous cells.
2. #REF! error value will return when the rows and columns offset reference over the edge of the worksheet.
Examples
Example 1: Basic usage for the OFFSET function
Return a reference to a cell with the below formula:
=OFFSET(B2,3,1)
In this case, B2 is the starting point, number 3 and 1 mean that to move 3 rows down and 1 column right from cell B2, and return the value in C5 finally. See screenshot:
Return a reference to a range of cells with the below formula:
=OFFSET(B2,3,1,2,2)
In this case, you will get the results of 2 x 2 range that is 3 rows below and 1 column right of cell B2.
Note: #VALUE! Error will occur when you only selecting one cell to apply the OFFSET function for returning a range of cells. You need to select a 2 x 2 range (says 4 blank cells), enter the formula and press the Ctrl + Shift + Enter keys to get the results.
Example 2: Use the OFFSET function to sum a range of values
As we mentioned above, if you try to use the OFFSET function such as =OFFSET(B2,3,1,2,2) on its own in a single cell, it will return a #VALUE! Error. However, if you combine the SUM and OFFSET function as below screenshot shown, it will return the sum of values in range C5:D6 directly.
1. Select a blank cell, copy the below formula into it and press the Enter key to get the result.
=SUM(OFFSET(B2,3,1,2,2)))
Example 3: Sum columns based on certain criteria
As below screenshot shown, how to get the total sales of Mango from Sun to Tue? Please try as below.
1. Select a blank cell, copy the below formula into it and press the Enter key to get the result.
=SUM(OFFSET(C2:E2,MATCH(G4,B3:B8,),))
Notes:
1. In the above formula, MATCH(G4,B3:B8) is looking for Mango and returning its position in range B3:B8. Let’s see, Mango locates 5 rows below cell B2, so it returns the number 5;
2. As the MATCH result is 5, and the last comma here represents 0, the OFFSET function now is displaying as OFFSET(C2:E2,5,0), which means that the offset starts from range C2:E2, move 5 rows down and 0 column right to get the reference to range C7:E7;
3. Then the SUM function displays as SUM(C7:E7), and finally return the sum of values in range C7:E7.
More Examples
How to sum every n rows down in Excel?
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.
- Designed for 1500 work scenarios, helps you solve 80% Excel problems.
- Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
- Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
- 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Firefox, And New Internet Explorer.
