In Excel, combine SUM and OFFSET to total a range whose size or position can change: for example, =SUM(OFFSET(B2,0,0,D1,1)) sums a number of cells specified in D1. OFFSET identifies the range; SUM adds its values. This can be useful for rolling totals and adjustable summaries, although OFFSET is volatile and alternatives may be better for large workbooks.
What SUM and OFFSET Do Together
SUM adds numbers in cells or ranges. OFFSET returns a reference to a cell or range located a specified number of rows and columns from a starting reference. Put OFFSET inside SUM, and the returned reference becomes the range SUM adds. OFFSET does not add or move cell contents; it describes where the cells to sum are.
The combination is useful when a total should respond to a control value, such as the number of periods to include, or when a formula needs to find a block of cells relative to a starting point. It is not necessary for ordinary fixed totals. If the range will always be B2:B10, use =SUM(B2:B10); that is simpler to read and maintain.
OFFSET has been available in Excel for many versions, and the formulas here work in Microsoft 365 for Windows. The formulas also work in current Excel for Mac and Excel for the web; the main differences are in how you navigate the interface, not in this calculation. Excel uses commas between arguments in the examples below. Some regional settings use semicolons instead.
How OFFSET Builds a Range
OFFSET takes a starting reference, a row shift, and a column shift. It can also take a height and width to return a larger range. The row and column shifts are relative to the starting cell: zero means stay in the same row or column, one means move down or right by one, and a negative number means move up or left. Height and width specify how many rows and columns the returned reference should cover.
For example, with B2 as the starting cell, a zero row shift and zero column shift keep the reference at B2. A height of three and width of one makes the reference B2:B4. If D1 contains 3, the formula =SUM(OFFSET(B2,0,0,D1,1)) sums three cells beginning at B2. With 120, 135, and 110 in B2:B4, the result is 365.
The final two OFFSET arguments are optional when only one cell is needed. For a SUM formula, it is often clearer to provide both height and width explicitly. A height and width must be positive; a zero or negative size does not define a usable range. The reference also has to remain within the worksheet grid.
Make the Number of Cells Adjustable
An adjustable height is a straightforward way to use SUM with OFFSET. Put a positive whole number in a control cell, then use that number as OFFSET’s height. This can let a reader change a summary period without editing the formula itself. The control cell should not exceed the number of available data cells.
For this example, assume B2:B4 contains 120, 135, and 110, and D1 contains 3. The formula =SUM(OFFSET(B2,0,0,D1,1)) returns 365 because it adds B2:B4. If D1 changes to 2, it adds B2:B3 and returns 255. This version always starts at B2; changing the height does not make the range move to the most recent entries.
To use a control cell for a range that ends at the last value in a list, the starting reference and row shift must be chosen accordingly. Check the intended first and last cells when building such a formula. A small test with known values is a good way to confirm that the formula is including the desired rows rather than merely returning a plausible-looking total.
Sum the Most Recent Entries
A common related question is how to total the latest few months or records. Suppose B2:B7 contains six consecutive numeric monthly values: 120, 135, 110, 150, 145, and 160. To total the last three, OFFSET can move down from B2 to B5 and return three cells. With that layout, =SUM(OFFSET(B2,COUNT(B2:B7)-3,0,3,1)) returns 455: it adds B5:B7, or 150 + 145 + 160.
Here, COUNT returns 6, so the row shift is 6 minus 3, or 3. The range is therefore three rows below B2 and has a height of three. This method assumes the entries are numeric and contiguous, with no blank cells or text among them. It also assumes there are at least three numbers. If there are fewer, the row shift can become negative and the intended calculation will fail or refer to the wrong area.
To make the number of latest values adjustable, assume the same six numbers are in B2:B7 and D1 contains 3. The formula =SUM(OFFSET(B2,COUNT(B2:B7)-D1,0,D1,1)) returns 455. Changing D1 to 2 makes it add B6:B7, for a result of 305. Keep D1 between 1 and the number of available numeric entries, and avoid gaps in the data if you rely on COUNT to calculate the starting position.
Expand a Total as Data Is Added
OFFSET can also be used when the bottom of a range changes. One approach is to calculate the range height from the number of filled cells, then use that height in OFFSET. This can work for a compact, consistently populated list, but it requires care: a count based on a column can be misleading if that column contains blanks, headings, notes, or formulas returning empty text.
For a total that should simply include new records, an Excel Table is often easier to maintain. Select the data and choose Insert > Table, making sure the header option matches the data. If a table is named Sales and its numeric column is Amount, a structured-reference total can be written as =SUM(Sales[Amount]). The table column reference expands as rows are added to the table, and the formula does not need to calculate a changing range height.
Tables are especially useful when data has clear column headings and new records are appended as rows. OFFSET may still suit a worksheet designed around a specific starting cell or a variable-size block. Choose based on how the data is organized, not simply on which formula looks shorter.
Common Errors and Limitations
The most frequent problem is an incorrect row shift. OFFSET counts from its reference, so an offset of one row from B2 starts at B3, not B2. When the result is wrong, temporarily inspect the range returned by OFFSET by entering it in a spare cell or selecting the relevant cells, then compare its boundaries with the intended range.
- Too few entries: A last-N formula needs at least N available values. A negative row shift or an invalid range can result if the data count is too small.
- Blank or nonnumeric cells: COUNT counts numeric cells, not every occupied cell. Gaps can make a count-based offset land in the wrong place.
- Wrong height or width: Reversing these arguments can return a range with an unintended shape. For a single column, use width 1.
- Data outside the reference: If new rows are added below the cells included in the formula’s counting range, the formula will not automatically count them.
- Errors in source cells: SUM does not ignore error values in the range; an error can flow through to the total.
OFFSET is volatile, which means Excel recalculates it when recalculation occurs, even if a change is not directly related to its reference cells. A workbook with many volatile formulas can recalculate more often than necessary. This does not make a single OFFSET formula inherently inaccurate, but it is a reason to prefer a nonvolatile alternative in a large or calculation-heavy workbook.
Alternatives to SUM OFFSET
For a fixed range, plain SUM is the clearest choice. For a last-three-values total, INDEX can create a range without OFFSET. With the same six consecutive numeric values in B2:B7, =SUM(INDEX(B2:B7,COUNT(B2:B7)-2):INDEX(B2:B7,COUNT(B2:B7))) returns 455 by summing the last three cells, B5:B7. This also assumes at least three contiguous numeric values; the COUNT-based positions can be wrong when blanks or nonnumeric entries are mixed into the list.
INDEX is nonvolatile, so it can be preferable when recalculation efficiency matters. It can also be less immediately intuitive because the colon between the two INDEX results forms the range to sum. Document the assumptions near the formula or use helper cells if other workbook users need to understand how the range is selected.
For growing lists, Excel Tables are often the most readable option. For a fixed-size rolling summary, a direct formula referencing the required cells may be simpler. In Microsoft 365, newer functions can help with other dynamic-array tasks, but they are not automatically a replacement for OFFSET: select a method that matches whether the range is fixed, expanding, or based on the latest entries.
How to Choose the Right Method
Before using SUM with OFFSET, identify what is expected to change. If neither the start nor the size changes, use SUM with a direct range. If the size is controlled by a cell, OFFSET can make that control explicit. If the range should include the latest records, make sure the formula can reliably identify the last record and that the source data has no gaps that undermine the count.
For an ongoing log, consider an Excel Table so its named column grows with the data. For a workbook with many formulas, consider INDEX or a table-based reference to avoid OFFSET’s volatility. Whichever approach you choose, test a small case where you can calculate the expected answer by hand, then test boundary situations such as the minimum number of entries and the addition of a new row.
Frequently Asked Questions
Can I use SUM with OFFSET to sum the last 12 months?
Yes, if the monthly values are consecutive numeric entries and there are at least 12 of them. Use the last-N pattern above with 12 in place of 3, and adjust the count range to cover the full data list. Check for blanks and make sure the range includes every month you expect.
Does OFFSET return a value or a range?
OFFSET returns a cell reference or range reference, not the cell contents as a standalone value. SUM can use that reference to add its values. The distinction matters because OFFSET describes where to look; SUM performs the addition.
Why is my SUM OFFSET formula returning the wrong total?
Check the anchor cell, row and column shifts, and height and width first. Then verify that the data is contiguous if the formula uses COUNT to locate the latest entries. A blank or text value can change what COUNT reports, while an off-by-one row shift can include an unintended cell.
Is OFFSET better than a direct SUM formula?
Not for a fixed range. A direct SUM is simpler and less prone to confusing range calculations. OFFSET is useful when the range’s location or size needs to respond to a control or changing data layout.
Is OFFSET volatile in Excel?
Yes. Excel treats OFFSET as a volatile function and recalculates it during recalculation. A few uses are usually straightforward, but in a large workbook with many dependent formulas, a table reference or nonvolatile approach such as INDEX may be more suitable.
Related
- Excel Sum Above: How to Add Cells Above Your Total
- Excel Max Rows per Sheet: Limits and Workarounds
- Excel Max Value in a Column: Find the Largest Number
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Find Repeated Names in Excel with COUNTIF FormulaTo find repeated names in Excel, put names in A2:A100, then enter =COUNTIF($A$2:$A$100,A2) in B2 and fill it…
- SUMIFS Excel Criteria Range: How to Set It UpIn Excel, a SUMIFS criteria range is the range of cells that SUMIFS checks against a condition before…
- Excel Sum Rows Based on Cell Value: Formulas That WorkTo total values in rows where a cell matches a condition, use Excel’s SUMIF function. With categories in…
- Excel Date Countdown Formula: Calculate Days Left in ExcelIf the target date is in cell B2, enter =B2-TODAY() in another cell to show the number of…
- Excel LOWER Formula: Convert Text to LowercaseUse Excel’s LOWER function to convert text to lowercase: if the original text is in A2, enter =LOWER(A2)…
- Named Ranges in XLOOKUP: How to Use Them in ExcelTo use a named range in XLOOKUP, replace a cell reference such as A2:A6 with the range’s name…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.