Sum last n columns

sum last n columns excel

To sum the last n columns in a range or a table, you can use the SUM and TAKES functions in Excel instead of long formulas.

How to sum the last n columns in Excel?

Steps to summarize the last n columns in a range:

  1. Open Excel.
  2. Type =SUM(TAKE(range,, -n))
  3. Press Enter
  4. The formula returns the sum of the last n columns.

Example

We want to summarize values in the last N columns using a dynamic array formula in the example. Try to get the sum of values using the last two columns!

Formula:

=SUM(TAKE(C2:G13,, -2))

sum last n columns excel

Explanation:

=SUM(TAKE(C2:G13,, -n))

  • C2:G13: This range selects the entire table from January to December (rows 2 to 13) and columns C to G.
  • The second argument, a comma, tells Excel you’re not changing the rows.
  • The third argument -n specifies that you want to extract the last n columns from the range. The negative sign (-) indicates that you’re taking columns from the right side of the range.

Workaround with SUM and FILTER Excel functions

What if you have Excel 2021 instead of Microsoft 365? The following method uses the SUM, FILTER, COLUMN, and COLUMNS functions.

Formula:

=SUM(FILTER(range, COLUMN(range)>COLUMNS(range)-(N+1)))

In the example, the range is B3:H10 and N=3; the formula looks like below:

=SUM(FILTER(B3:H10, COLUMN(B3:H10)>COLUMNS(B3:H10)-3+1))

filter workaround to sum last n columns

Let’s break the formula down!

  1. COLUMN(B2:G13): This part of the formula generates an array of the column numbers for the range B2:G13. In this case, for columns B to G, the result will be {2, 3, 4, 5, 6, 7} because these are the column indices of Excel for columns B through G.
  2. COLUMNS(B2:G13): The COLUMNS function returns the total number of columns in the specified range. In this case, B2:G13 spans 6 columns so that this part will return 6.
  3. COLUMNS(B2:G13) – n + 1: If n = 2, this calculation becomes 6 – 2 + 1 = 5. This means we are looking for column numbers greater than or equal to 5. The result will include columns 5 and 6 (corresponding to columns F and G in B2).
  4. COLUMN(B2:G13) > COLUMNS(B2:G13) – n + 1: This part of the formula creates a logical array that evaluates whether the column numbers (from COLUMN(B2:G13)) are greater than the threshold calculated earlier (e.g., 5). Example Logical Array: {FALSE, FALSE, FALSE, FALSE, TRUE, TRUE}. This indicates that only the last two columns (F and G) will be included in the sum because their column numbers (5 and 6) are greater than or equal to 5.
  5. FILTER(B2:G13, COLUMN(B2:G13) > COLUMNS(B2:G13) – n + 1): This part filters the columns in the range B2:G13 based on the logical condition generated in the previous step. Only the columns where the condition is TRUE are included in the result. If n = 2, this will filter out all columns except for the last two (columns F and G).
  6. SUM(FILTER(…)): Finally, the SUM function adds up all the values in the filtered range. It will sum all the values in the selected columns for all rows in B2:G13.

Using SUMCOLUMNS user-defined function

You can easily implement a user-defined function into your Excel Workbook. This function is perfect for older Excel versions, such as Excel 2019 or 2016. It is good to know that user-defined functions are compatible with all Excel versions.

  1. Write the formula in cell B3
  2. =SUMCOLUMNS(B10:B20, n,TRUE)
  3. Press Enter
  4. The function will summarize the last n columns in the selected range.

To sum the first n columns in a selected range, change the formula and apply the optional argument. Set the “firstColumns” argument to TRUE.

Formula:

=SUMCOLUMNS(B3:H10,3, TRUE)

If you want to use the SUMCOLUMNS function, copy and paste the code below into a new module.

Function SUMCOLUMNS(rng As Range, n As Long, Optional firstColumns As Long = 0) As Double
    Dim row, col As Long
    Dim sum As Double

    sum = 0
    For row = 1 To rng.Rows.count
        If firstColumns = 0 Then
            col = rng.Column + rng.Columns.count - n
            sum = sum + WorksheetFunction.sum(Range(Cells(rng.row + row - 1, col), _
            Cells(rng.row + row - 1, rng.Column + rng.Columns.count - 1)))
        Else
            col = rng.Column
            sum = sum + WorksheetFunction.sum(Range(Cells(rng.row + row - 1, col), _
            Cells(rng.row + row - 1, col + n - 1)))
        End If
    Next row

    SUMCOLUMNS = sum
End Function