How to use INDIRECT or OFFSET to sum monthly data by year in Excel
Question details
The user needs to convert 20 years of monthly values into annual totals in Excel using formulas that dynamically advance the source range by 12 rows for each year, rather than using hard-coded references.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has monthly data spanning 20 years and needs to create annual totals without manually editing each formula.
- Observed behavior
- The formulas should dynamically advance the source range by 12 rows for each year instead of using hard-coded references.
Confirm your monthly data is in one continuous column, with each year occupying exactly 12 consecutive rows. Identify the first cell where the annual total should appear.
Use OFFSET to create dynamic annual sum formulas
Best when your monthly values are in a single column and you want a compact formula that advances 12 rows per year.
OFFSET returns a range that starts 12 rows lower for each row you fill down, so the formula can be reused for all 20 years without changing references.
Select the first result cell, for example D1, and enter =SUM(OFFSET($A$1:$A$12,12*(ROW(D1)-ROW($D$1)),0)) if monthly data starts in A1. This sums A1:A12 for year 1.
Drag the fill handle from D1 down to D20. Each row increases the ROW difference by 1, which shifts the OFFSET range down by 12 rows per year.
Check that D2 refers to A13:A24, D3 refers to A25:A36, and so on, so each annual total covers the correct 12 monthly values.
Use INDIRECT with ROW to build a dynamic sum range
Use this when you prefer a text-based reference that constructs each 12-row range as a string.
Sum monthly data by year in WPS Office Spreadsheets with OFFSET or INDIRECT
WPS Office Spreadsheets supports Excel-compatible OFFSET and INDIRECT formulas, so you can create annual totals from 20 years of monthly data without manual range edits.
- 1. Open your monthly data workbook: Launch WPS Office Spreadsheets and open the workbook that contains the monthly values and the sheet where you want annual totals.
- 2. Select the first annual total cell: Click the cell where the first yearly total should appear, for example D1 or the first blank cell beside your year labels.
- 3. Enter the OFFSET or INDIRECT formula: Type =SUM(OFFSET($A$1:$A$12,12*(ROW(D1)-ROW($D$1)),0)) for OFFSET, or the equivalent INDIRECT formula, based on your preferred method.
- 4. Fill the formula down: Drag the fill handle down through the 20 annual total cells to apply the dynamic 12-row shift to each year.
- 5. Check the annual totals: Review a few calculated ranges to confirm each year sums the correct 12 monthly values before relying on the results.
Frequently Asked Questions
What is the difference between OFFSET and INDIRECT for this task?
OFFSET returns a reference directly based on row and column offsets, while INDIRECT builds a reference from a text string. Both can shift the sum range by 12 rows, but INDIRECT is volatile and may recalculate more often.
How do I adjust the formula if my data does not start in row 1?
Change the base cell and offset logic. For OFFSET, replace $A$1:$A$12 with the actual first 12-month range and adjust the 12*(ROW(...)-ROW(...)) offset so the first result still returns that range.
Can I use these formulas in WPS Office Spreadsheets?
Yes. WPS Office Spreadsheets supports OFFSET and INDIRECT formulas and can open Excel workbooks in .xlsx and .xls format.
Will the formula update automatically if I add another year of monthly data?
Existing formulas recalculate when monthly values change, but you must copy the formula down into a new annual total row for each additional year.
Why does my OFFSET formula show #VALUE! or #REF!?
This usually means the row offset moves the range outside the worksheet or the referenced cell is not a valid range. Check that the starting range and row calculation match your actual data layout.
