Running total
| A | |
|---|---|
| 1 | Value |
| 2 | 5 |
| 3 | 20 |
| 4 | 12 |
Formula: C2: =SCAN(0,A2:A4,LAMBDA(a,x,a+x))
Result: Spills down: 5, 25, 37
The totals after each item are 5, 25 and 37.
Google Sheets function
SCAN in Google Sheets
SCAN returns all the running results as an array, which spills natively in Google Sheets.
Syntax (same in Google Sheets)
SCAN(initial_value, array, LAMBDA(total, x))It replaces the helper column that holds a cumulative total copied down the sheet.
For a fuller explanation of the arguments and common errors, see the SCAN Excel guide.
Worked examples in Google Sheets
Highest so far
| A | |
|---|---|
| 1 | Value |
| 2 | 5 |
| 3 | 20 |
| 4 | 12 |
Formula: C2: =SCAN(0,A2:A4,LAMBDA(a,x,MAX(a,x)))
Result: Spills down: 5, 20, 20
The maximum rises to 20 and stays there when 12 arrives.
Compound growth
| A | |
|---|---|
| 1 | Rate |
| 2 | 0.1 |
| 3 | 0.1 |
| 4 | 0.1 |
Formula: C2: =SCAN(100,A2:A4,LAMBDA(a,r,ROUND(a*(1+r),2)))
Result: Spills down: 110, 121, 133.1
Starting from 100, each 10% step multiplies the balance again; ROUND keeps cents.
Try it
A tiny spreadsheet with example data. Edit the formula or any number and see the result.
Formula in F2: =SCAN(0,C2:C7,LAMBDA(a,x,a+x))
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Product | Units | Price | Result | |
| 2 | East | Pen | 120 | 2.5 | 120 | |
| 3 | West | Pen | 80 | 2.5 | 200 | |
| 4 | East | Book | 45 | 12 | 245 | |
| 5 | North | Book | 60 | 12 | 305 | |
| 6 | West | Lamp | 30 | 25 | 335 | |
| 7 | East | Lamp | 15 | 25 | 350 |
Result in F2: 120
Frequently asked questions
- Can SCAN make a running total in Google Sheets without a helper column?
- Yes. SCAN(0, range, LAMBDA(total, x, total + x)) in one cell fills the cumulative totals below it.
Google Sheets is used here only as a descriptive product name. FormulaCamp is not affiliated with Google.