Sum last n columns

To sum last n columns in a range or a table, you can use the SUMCOLUMNS function in Excel instead of long formulas.

How to Sum last n columns in Excel

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

SUMCOLUMNS function

SUMCOLUMNS is a user-defined function that lets you write a simple, easy-to-understand formula when you want to sum the last n columns or first n columns in a selected range.

Syntax:

=SUMCOLUMNS(range, n, [optional TRUE or FALSE])

Arguments:

The function uses two required and one optional argument.

• “Range”: range of cells that contain numeric values.
• “n”: the number of columns you want to sum.
• “firstColumns”: optional argument. In the case of TRUE, the function sums the last n columns. In the case of FALSE (default), the function will sum the last n columns.

Example to sum last n columns

In the example, we have 7 columns in the B3:H10 range, and the goal is to sum the last 3 columns.

Configure the arguments:

• Range = B3:H10
• n = 3
• firstColumns: We do not use the optional argument since the default value is FALSE.

Formula:

=SUMCOLUMNS(B3:H10,3)

Workaround with SUM and FILTER Excel functions

What if you want to sum the last n columns in Excel and are unfamiliar with user-defined functions? The following method uses the SUM, FILTER, COLUMN, and COLUMNS functions.

Generic 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))

Evaluate the formula from the inside out:

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

The expression returns an array that contains boolean (TRUE and FALSE) values:

{FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, TRUE}

This array will be the second argument of the filter function:

= FILTER(B3:H10, {FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, TRUE}) 

The FILTER function returns a filtered range that contains the last N=3 columns:

Finally, the SUM function calculates the sum of values in the last n columns.

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

The result is 320, which is equal to the SUMCOLUMNS function result.

How to sum first n columns

1. Write the formula in cell B3
2. =SUMCOLUMNS(B10:B20, n,TRUE)
3. Press Enter
4. The function will return the last n columns in the 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)

Result:

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

Additional resources

• SUM every nth column