Sum Last n Columns in Excel: Easy Formulas
This guide explains how to sum the last n columns in a spreadsheet, whether you need the most recent 3 months of sales, the last 5 test scores, or any other trailing set of columns. You will learn the exact formulas for Excel and Google Sheets, how they work, and how to adapt them when your data has blanks or errors.
Formula
For data in row 2 with values in columns A through Z, to sum the last 3 columns (X, Y, Z):
=SUM(OFFSET(A2,0,COUNT(A2:Z2)-3,1,3))
Here is what each part does:
- COUNT(A2:Z2) – counts how many numbers are in the row. If you have 26 columns with data, it returns 26.
- COUNT(A2:Z2)-3 – subtracts the number of columns you want to sum (3) to find the starting offset from column A.
- OFFSET(A2, 0, offset, 1, 3) – starts at A2, moves right by the offset, and returns a range that is 1 row tall and 3 columns wide.
- SUM(…) – adds up the values in that range.
If your data starts in a different column (say B), replace A2 with B2 and adjust the range in COUNT accordingly. Always use the same row for the COUNT and the OFFSET.
Worked Example
Suppose row 2 contains monthly sales from January to June: 100, 150, 200, 250, 300, 350. You want the sum of the last 3 months (April, May, June).
- COUNT(A2:F2) returns 6 (six numbers).
- 6 – 3 = 3, so OFFSET starts at A2 and moves 3 columns right, landing on D2.
- OFFSET(A2,0,3,1,3) returns the range D2:F2.
- SUM(D2:F2) = 250 + 300 + 350 = 900.
If you change the 3 to a 4, the formula will sum the last 4 months (March through June) and give you 1100. The formula updates automatically when you add new columns at the end, as long as the range in COUNT includes them.
When to Use This Formula
Use this approach when you have a row of numeric data and you always want the most recent n values, regardless of how many total columns there are. Common cases:
- Summing the last 4 weeks of sales from a weekly tracker.
- Adding up the last 3 test scores for each student.
- Calculating a rolling 12-month total from a monthly report.
The formula works because COUNT ignores empty cells and text, so it only counts numeric entries. If your row has text headers in the same row, they are ignored by COUNT.
How to Choose n
The number n is the count of columns you want to include. You can hardcode it (like 3) or put it in a cell so you can change it without editing the formula. For example, if cell H1 contains 5, use:
=SUM(OFFSET(A2,0,COUNT(A2:Z2)-H1,1,H1))
This is useful when you want to test different window sizes. Just change H1 and the formula recalculates.
Common Mistakes and Fixes
- Mixing rows: The COUNT range and the OFFSET reference must be on the same row. If your data is in row 5, use COUNT(A5:Z5) and OFFSET(A5,…).
- Blank cells in the middle: COUNT only counts numbers, so if a column is blank, it is not counted. This shifts the window. If you have blanks that should be treated as zeros, use COUNTA instead of COUNT, but then text will also be counted.
- Errors in the row: If any cell in the OFFSET range contains an error (like #DIV/0!), the SUM will return that error. Use IFERROR to handle it:
=IFERROR(SUM(OFFSET(...)),0). - Data starts in column B: Adjust the starting reference and the COUNT range. For column B, use
=SUM(OFFSET(B2,0,COUNT(B2:Z2)-n,1,n)).
Alternatives: SUMIF and Dynamic Arrays
If you have a header row with dates or labels, you can use SUMIF to sum columns where the header meets a condition. For example, to sum the last 3 months based on dates in row 1:
=SUMIF(A1:Z1,">"&DATE(YEAR(TODAY()),MONTH(TODAY())-3,1),A2:Z2)
In Excel 365 or Google Sheets, you can also use the TAKE function (Excel 365) or QUERY (Google Sheets) to get the last n columns. For Excel 365:
=SUM(TAKE(A2:Z2,,-3))
TAKE with a negative column count returns the last 3 columns. This is simpler and does not require COUNT or OFFSET, but it is only available in newer versions.
Frequently Asked Questions
Can I sum the last n columns in Google Sheets?
Yes, the OFFSET formula works in Google Sheets exactly as in Excel. You can also use =SUM(CHOOSECOLS(A2:Z2,COLUMNS(A2:Z2)-2,COLUMNS(A2:Z2)-1,COLUMNS(A2:Z2))) but that is clunky. The simplest is to use QUERY: =SUM(QUERY(A2:Z2,"select * offset "&COLUMNS(A2:Z2)-3)) but that returns rows, not columns. For columns, use =SUM(INDEX(A2:Z2,,COLUMNS(A2:Z2)-2):INDEX(A2:Z2,,COLUMNS(A2:Z2))).
What if my data has text in some cells?
COUNT ignores text, so if you have text in the row, the window may shift. If you want to include text as zero, use COUNTA instead. But be careful: COUNTA counts any non-empty cell, including text and errors.
How do I sum the last n columns across multiple rows?
If you have a table with multiple rows, you can copy the formula down for each row. The formula uses relative references, so it will adjust to each row. For a single total across all rows, use SUMPRODUCT with OFFSET, but that gets complicated. Simpler: add a helper column that sums each row, then sum that column.
Why does my formula return 0?
Check that the COUNT range includes all your data. If your data goes beyond column Z, expand the range. Also, ensure the OFFSET height and width are correct. If n is larger than the number of columns, the OFFSET will go beyond the sheet and return #REF!.
Can I use this with a dynamic range that grows?
Yes, if you use a full-column reference like A:A, but that will include headers and might cause circular references. Better to use a named range or an Excel Table. With a Table, you can use structured references, but the OFFSET approach still works if you reference the table column range.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.