Running Total of Monthly Sales Both Ways in Excel and SQL

A running total answers "how much so far this year?" It is one of the first calculations people build in a spreadsheet and one of the first they have to port to a database when the sheet outgrows itself. The two versions look nothing alike, and each has a trap: in Excel, a total that quietly depends on the sheet being sorted, and in SQL, a default window frame that makes equal dates produce equal totals. This article builds the monthly cumulative sales figure in both, with the restart per year and the checks. It sits among the worked pairs in moving between Excel and SQL, and running totals in Excel with SQL window functions is the shorter introduction.

Sample data

Monthly sales, already aggregated, in A2:B7 (month in A as a date, amount in B) and as a table monthly_sales(month, amount):

The running totals are 100, 250, 330, 450, 540 and 650.

Excel: the expanding range

The classic formula anchors the first cell and lets the last move:

=SUM($B$2:B2)

In row 2 it sums B2 to B2, in row 3 B2 to B3, and so on when filled down. It is easy to read and works in every version. Its weakness is its dependence on row order: if someone sorts the sheet by amount, the "running total" runs down the sorted list and means nothing. And if a row is inserted above the first or a row is deleted, the anchor can shift. Put the data in a table and use the structured version, which keeps the logic in one column:

=SUM(INDEX([amount], 1):[@amount])

INDEX([amount], 1) refers to the first cell of the column and [@amount] to the current row, so the range runs from the top to the current row. It survives sorting only if the table is sorted by month again, so lock the order by sorting before use or choose the date-based formula below.

Excel: SUMIFS bounded by date

A formula that depends on the date, not on the row position, holds up when the sheet is sorted:

=SUMIFS($B$2:$B$7, $A$2:$A$7, "<="&A2)

It sums all amounts for months up to and including this one. Sorted or not, the result is right. For a restart each year, add a year condition:

=SUMIFS($B$2:$B$7, $A$2:$A$7, "<="&A2, $A$2:$A$7, ">="&DATE(YEAR(A2),1,1))

The second condition starts the sum at January 1 of the row's year. This formula scans the whole range for each row, so on thousands of rows it is slow; for large sheets prefer the query. It also needs a unique date per row, or equal dates all see each other's amounts, which is the behavior you want if equal dates are legitimate and want to share a total.

Excel: SCAN in Microsoft 365

Dynamic arrays give a one-cell formula:

=SCAN(0, B2:B7, LAMBDA(acc, v, acc + v))

SCAN walks the array, carrying an accumulator, and returns every intermediate value, spilling the six totals down. The result depends on the order of the array, as before, so sort first or use SCAN over SORTBY(B2:B7, A2:A7). It needs a recent Excel and no helper column (LET and LAMBDA for readable formulas in Excel workbooks explains the lambda helpers).

SQL: a window function

SELECT month, amount,
       sum(amount) OVER (ORDER BY month) AS running_total
FROM monthly_sales
ORDER BY month;

sum(...) OVER (ORDER BY month) computes, for each row, the sum over the rows in the window. With the default frame, that window is "from the start of the partition to the current row and its peers", where peers are rows with the same ORDER BY value. For one row per month, there are no peers and the result is the six totals above.

The default frame and ties

The detail in "and its peers" matters whenever the sort key repeats. Suppose the data were daily and two orders share a date:

With OVER (ORDER BY ordered_on) and the default frame (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), both March 2 rows are peers, so each shows 100 (50 + 30 + 20): the total through the end of that date. If you wanted the total to step row by row, 80 then 100, you must say ROWS and break the tie:

sum(amount) OVER (ORDER BY ordered_on, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

ROWS counts physical rows, and the extra sort column makes the order deterministic. Which behavior is right depends on the question: "total through this date" suits the default, and "total through this order" needs ROWS. Excel's expanding range always behaves like ROWS; its SUMIFS on date behaves like the default frame. The two tools can disagree for this reason alone.

Restarting per year or per group

SELECT month, amount,
       sum(amount) OVER (PARTITION BY date_part('year', month) ORDER BY month) AS ytd
FROM monthly_sales;

PARTITION BY restarts the total for each year, giving a year-to-date figure. The same applies to any grouping: customer, region, product. For both a group and a year, list both: PARTITION BY region, date_part('year', month). In Excel the equivalent is the SUMIFS with additional criteria, as above.

Aggregate first, then accumulate

If the raw data is at order level, a running total of months needs the months first. Two approaches give the same answer for monthly output, and one is easier to get wrong.

WITH m AS (
  SELECT date_trunc('month', ordered_on)::date AS month, sum(amount) AS amount
  FROM orders
  GROUP BY 1
)
SELECT month, amount, sum(amount) OVER (ORDER BY month) AS running_total
FROM m
ORDER BY month;

The common table expression aggregates to one row per month, and the window accumulates over those rows. Doing it in one step by putting sum(amount) OVER (...) over the raw orders and then grouping gives the cumulative total at the order level, which is not what a monthly report shows. Be careful, too, with months in which nothing happened: GROUP BY produces no row for them, so the running total has no row for that month. Generate the calendar with generate_series and left join, as in charts from data pulled out of databases, if the report needs every month.

In Excel, the matching pattern is a pivot table or SUMIFS by month first, and then the running total column on that summary, not on the raw rows.

Checking the result

Three checks catch nearly all errors. The last running total must equal the sum of the column (650 for the sample). The running total must never decrease if every amount is non-negative. And the total at the end of each partition must equal the plain sum for that group. Compare the sheet and the query on the same sample: six totals, in order, matching. Then try a tie, a missing month and an out-of-order sheet, and note what each tool does.

Showing it

A cumulative line always rises, which hides short-term movement. Pair it with the monthly series, either in the same chart as bars for months and a line for the total, or as two charts. A cumulative figure compared with a target line or last year's cumulative gives the reader something to judge by. State the reset point (calendar year, fiscal year) in the title (choosing Excel chart types for query results).

When a running total is the wrong measure

It is the wrong measure when the question is about the rate: a cumulative line buries the fact that sales fell this month. It is wrong when late-arriving data restates earlier months, because every later total changes. And it is wrong for averages or ratios, which do not accumulate by adding; a running average needs the running sum divided by the running count. For the SQL syntax in detail, the PostgreSQL manual's window function tutorial and the section on window frames explain RANGE and ROWS, and Microsoft's page on SCAN covers the Excel function.