How to create a running total with QUERY in Excel and Google Sheets

Heads up: Excel and Google Sheets do this differently.

Step by step

1. 1Sort your data by the column you want to accumulate over — use QUERY itself for this: paste =QUERY(A1:B10,"SELECT A, B ORDER BY A",1) into D1:E10 to get a date-ordered copy.
2. 2In column F (next to the QUERY output), enter the running total formula for the first data row: =SUMIF(D$2:D2,D2,E$2:E2) — the expanding lock ($2:D2 grows as you drag down).
3. 3Drag the running total formula down to cover all output rows.
4. 4For a fully array-based approach in Sheets without dragging, use ARRAYFORMULA with MMULT: =ARRAYFORMULA(MMULT((ROW(E2:E7)>=TRANSPOSE(ROW(E2:E7)))*1,E2:E7)) to get cumulative sums in one formula.
5. 5Label the result column 'Running Total' by typing the label directly in F1, since QUERY's LABEL clause only applies inside the QUERY string itself.

Tips

• The MMULT approach is elegant but recalculates an n×n matrix — for more than ~500 rows it can slow down. Use a dragged SUMIF instead for large datasets.
• If your dates are not unique (multiple rows per date), SUMIF accumulates all rows for that date together. Pre-aggregate with QUERY's GROUP BY before calculating the running total to avoid double-counting.
• In Excel, the expanding-anchor SUMIF pattern =SUMIF(B$2:B2,B2,C$2:C2) works identically — copy it down the column and Excel locks the top of the range while the bottom expands.

Frequently asked