Running Total (Cumulative SUM)

running-total-cumulative-sum-in-excel

This tutorial will show you how to calculate the running total (cumulative sum) in Excel using the RTOTAL function.

What is a running total?

The running total is a cumulative sum of the selected values. If you have n values, the first record is n1. The second is SUM(n1+n2), the third is SUM(n1 + n2 + n3), and so on. You can calculate it row-by-row or column-by-column using formulas.

How to calculate the running total in Excel?

Here are the steps to calculate the cumulative sum:

  1. Enter the =RTOTAL(B3:B14) formula.
  2. Select the range where you want to calculate the cumulative sum.
  3. RTOTAL function returns with the running total.

It is good to know that RTOTAL is a dynamic array function; Excel will spill the result into an array.

Syntax:

=RTOTAL(range)

Arguments:

RTOTAL function uses a single, required argument: the selected range.

Example

In the example, we have the sales data in column B. Use the RTOTAL function and apply the following formula:

=RTOTAL(B3:N14)

The function will return the cumulative sum of values in each row.

running-total-cumulative-sum-in-excel

Explanation: Let us analyze the result. In cell C3, the function returns with the first value in a range. Cell C4 contains the second value in the “running total” column.

=C4 = B3+B4 = 2000 + 3000 = 5000

Following the logic mentioned earlier:

=C5 = B3+B4+B5 = 2000 + 3000 + 8000 = 13000

Note: The RTOTAL function works fine even if you use columns to calculate the running total. In the following example, you want to calculate the cumulative sum horizontally.

Formula:

=RTOTAL(C2:H2)

calculate-cumulative-sum-column-excel

How to implement the user-defined function? You can copy the function to your Workbook. Press Alt + F11 to open the VBA editor. Create a new module and paste the code.

Here is the code of the RTOTAL function:

Function RTOTAL(dRange As Range, Optional n As Boolean = True) As Variant
    Dim cRow As Long
    Dim rt As Double
    Dim r() As Double
    If dRange.Rows.count = 1 Then
        ReDim r(1 To dRange.Columns.count)
        For cRow = 1 To dRange.Columns.count
            rt = rt + dRange.Cells(1, cRow).Value
            r(cRow) = rt
        Next cRow
        RTOTAL = r
    Else
        ReDim r(1 To dRange.Rows.count)
        rt = 0
        For cRow = 1 To dRange.Rows.count
            rt = rt + dRange.Cells(cRow, 1).Value
            r(cRow) = rt
        Next cRow
        If n Then
            RTOTAL = Application.Transpose(r)
        Else
            RTOTAL = r
        End If
    End If
End Function

Using the SUM function

To manually create a formula, you must use the SUM function and apply absolute and relative references. In the example, use the following formula:

=SUM($B$3:B3)

Good to know that $B$3 is an absolute reference and B3 is a relative reference. Click inside the formula and press F4 twice to fix the B3 cell.

copy-the-formula-down

Additional resources and related formulas: