Try the example, then check the result.
Download the fictional material and follow the steps below. The video uses English illustrative diagrams; it does not present a live app recording. Product controls may vary by version.
Check the answer before applying the method to your own data. How we review guides ↗
Watch the complete lesson.
The video uses English illustrative diagrams; it does not present a live app recording. English captions. Product menus may differ in your account.
Quick answer. For a contiguous numeric amount list in B2:B1000, place =SCAN(0,FILTER(B2:B1000,B2:B1000<>""),LAMBDA(total,amount,total+amount)) in C2. Keep the spill column empty and append new amounts within the covered rows; expand both B2:B1000 references when the list exceeds row 1000.
Use the fictional practice file and independent answer check. Work in a copy or a permitted demo account.
Before you start. This bounded formula covers B2:B1000 only. Expand both range references if your list grows beyond row 1000. It assumes one contiguous list of numeric amounts with no blank rows inside it. FILTER removes blank amount cells, so results can lose row alignment if blanks are interspersed. Do not put manual values below C2 where the spilled totals need room. Formula separators and function names can vary by spreadsheet locale; the downloadable example uses English (US) syntax.
1. Check the starting arithmetic
Load the fictional CSV into a disposable Google Sheet and keep amounts in B2 through B4: five, negative two, and seven. Before entering a formula, add them on paper: five, then three, then ten. This independent check catches an incorrect cumulative formula even if its output looks plausible.
Checkpoint: Expected starting totals: 5, 3, 10.
2. Enter one bounded formula
Leave C2 and the cells below it empty. In C2 enter =SCAN(0,FILTER(B2:B1000,B2:B1000<>""),LAMBDA(total,amount,total+amount)). SCAN returns every intermediate sum, while FILTER sends it only nonblank amounts from the bounded B2:B1000 range. The two Lambda names are placeholders for the carried total and current amount.
Checkpoint: One formula spills three results into C2:C4.
3. Verify every current row
Read C2, C3, and C4: five, three, and ten. A common error is using a plain SUM over the entire column in every row, which repeats the final total rather than displaying each intermediate balance. Another is typing into the spill range, which blocks the array result.
Checkpoint: C2=5, C3=3, C4=10.
4. Append a fresh amount
Add Adjustment with amount four in the next contiguous row, B5, which is inside the B2:B1000 source range. Do not copy the C2 formula. The existing SCAN result should grow into C5 and display fourteen. If it does not, check that the B5 amount is a number rather than text, and that C5 has no blocking value.
Checkpoint: New C5=14 without fill-down.
5. Know the range limit
The source range ends at B1000. If your data grows beyond that row, expand both B2:B1000 references in the formula. If your data includes blank separator rows, FILTER compresses them and the running results no longer align with the original row labels. For that layout, choose a row-aware formula or a conventional fill-down method instead.
Checkpoint: Works for a contiguous numeric list within B2:B1000.
Verify the result
With the first three amounts 5, -2, and 7, the running totals are 5, 3, and 10. Appending a fourth contiguous row named Adjustment with amount 4 within the B2:B1000 source range produces a fourth running total of 14 without copying a formula. The formula is in C2 and its spill range is otherwise empty.
If it still does not work
This bounded formula covers B2:B1000 only. Expand both range references if your list grows beyond row 1000. It assumes one contiguous list of numeric amounts with no blank rows inside it. FILTER removes blank amount cells, so results can lose row alignment if blanks are interspersed. Do not put manual values below C2 where the spilled totals need room. Formula separators and function names can vary by spreadsheet locale; the downloadable example uses English (US) syntax.
Official sources
- SCAN function in Google Sheets (checked 20 October 2026).
- LAMBDA function in Google Sheets (checked 20 October 2026).
Any video panel without a marked actual-app capture is an illustrative diagram of the fictional exercise.
Practice downloads.
The exercise files contain invented information. Save a local copy before editing.