Spreadsheet Practice

How to calculate a running total with ARRAYFORMULA in Google Sheets

Google Sheets formula

=ARRAYFORMULA(IF(B2:B12="","",SUMIF(ROW(B2:B12),"<="&ROW(B2:B12),B2:B12)))

Sum every row up to the current one with SUMIF on row numbers: =ARRAYFORMULA(IF(B2:B12="","",SUMIF(ROW(B2:B12),"<="&ROW(B2:B12),B2:B12))).

Result on the example
a 11-row list that spills from C2 in C2
Excel
See below

See it work

The formula is already in C2. Change any input and the result updates, or edit the formula to experiment.

How it works

For each row, SUMIF adds up the amounts whose row number is less than or equal to that row's number: row 2 sums one amount, row 3 sums two, and so on. ARRAYFORMULA runs that once per row, and the IF keeps the spare rows at the bottom blank until an amount is entered.

=ARRAYFORMULA(IF(B2:B12="","",SUMIF(ROW(B2:B12),"<="&ROW(B2:B12),B2:B12)))

Result{11 rows × 1 col} 1250, 1680, 2555, 4655, …

  1. B2:B1211 rows × 1 col: $1,250, $430, $875, $2,100, …

    A range: 11 cells in one column.

  2. B2:B12=""{11 rows × 1 col} FALSE, FALSE, FALSE, FALSE, …

    The = operator tests whether the two sides are equal.

  3. ROW(B2:B12){11 rows × 1 col} 2, 3, 4, 5, …

    ROW: Row number of a reference, or of this cell.

    • referenceB2:B1211 rows × 1 col: $1,250, $430, $875, $2,100, …
  4. ROW(B2:B12){11 rows × 1 col} 2, 3, 4, 5, …

    ROW: Row number of a reference, or of this cell.

    • referenceB2:B1211 rows × 1 col: $1,250, $430, $875, $2,100, …
  5. "<="&ROW(B2:B12){11 rows × 1 col} "<=2", "<=3", "<=4", "<=5", …

    The & operator joins as text.

  6. SUMIF(ROW(B2:B12),"<="&ROW(B2:B12),B2:B12){11 rows × 1 col} 1250, 1680, 2555, 4655, …

    SUMIF: Adds values where the range meets a condition.

    • rangeROW(B2:B12){11 rows × 1 col} 2, 3, 4, 5, …
    • criteria"<="&ROW(B2:B12){11 rows × 1 col} "<=2", "<=3", "<=4", "<=5", …
    • sum_rangeB2:B1211 rows × 1 col: $1,250, $430, $875, $2,100, …
  7. IF(B2:B12="","",SUMIF(ROW(B2:B12),"<="&ROW(B2:B12),B2:B12)){11 rows × 1 col} 1250, 1680, 2555, 4655, …

    IF: Returns one value if a test is true, another if false.

    • logical_testB2:B12=""{11 rows × 1 col} FALSE, FALSE, FALSE, FALSE, …
    • value_if_true""
    • value_if_falseSUMIF(ROW(B2:B12),"<="&ROW(B2:B12),B2:B12){11 rows × 1 col} 1250, 1680, 2555, 4655, …
  8. ARRAYFORMULA(IF(B2:B12="","",SUMIF(ROW(B2:B12),"<="&ROW(B2:B12),B2:B12))){11 rows × 1 col} 1250, 1680, 2555, 4655, …

    ARRAYFORMULA: Runs a formula over whole ranges and returns all the results.

    • array_formulaIF(B2:B12="","",SUMIF(ROW(B2:B12),"<="&ROW(B2:B12),B2:B12)){11 rows × 1 col} 1250, 1680, 2555, 4655, …

In Excel

In Excel, fill =SUM($B$2:B2) down, or in Microsoft 365 use =SCAN(0,B2:B9,LAMBDA(a,x,a+x)).

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=ARRAYFORMULA(SUMIF(ROW(B2:B12),"<="&ROW(B2:B12),B2:B12))spills 11 × 1Different resultWithout the blank test, the final total repeats down the empty rows. 8,100 repeated in rows 10 to 12.

Common mistakes

  • The total follows the row order, not the dates. Sort the data by date first, or the running total jumps around.
  • On thousands of rows this recalculates slowly, since every row sums all the rows above it. Newer Sheets has SCAN: =SCAN(0,B2:B12,LAMBDA(total,x,total+x)).
  • Point it at an open range like B2:B to cover future rows, but keep the IF, or every empty row to the bottom of the sheet shows the final total.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Calculate a running total with ARRAYFORMULA

In C2, write one formula that shows the running total of the amounts in column B for every row down to row 12, leaving empty rows blank.

Questions

How do I restart the total for each category?

Sum only the rows above with the same category, filled down from C2: =SUMIFS($B$2:B2,$A$2:A2,A2).

Can I do it with a simple fill-down formula?

Yes: =SUM($B$2:B2) in C2, filled down. ARRAYFORMULA saves you from refilling when rows are added.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.