SCAN function
Quick answer
SCAN works like REDUCE, passing a running result and each value to a LAMBDA, but it returns the running result at every step instead of just the last one.
- Syntax
=SCAN([initial_value], array, lambda)- Most common use
=SCAN(0,B2:B5,LAMBDA(total,v,total+v))returns $1,200 (A running total, spilled down)
That makes it a one-formula running total, running maximum or running balance that spills next to your data.
- Available in
- Excel 2024 and Microsoft 365
- Category
- Dynamic array functions
- Returns
- An array the same size as the input, with the running result at each value.
- Google Sheets
- Available in Google Sheets.
Syntax
=SCAN([initial_value], array, lambda)
| Argument | Required? | What it is |
|---|---|---|
initial_value | Optional | The starting value of the running result; 0 if left empty. |
array | Required | The values to walk through, row by row. |
lambda | Required | A LAMBDA with two inputs: the running result so far, and the next value. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| C2 | =SCAN(0,B2:B5,LAMBDA(total,v,total+v)) | $1,200spills 4 × 1 | A running total, spilled down |
| D2 | =SCAN(0,B2:B5,LAMBDA(m,v,MAX(m,v))) | $1,200spills 4 × 1 | The best month so far |
| F2 | =INDEX(SCAN(0,B2:B5,LAMBDA(total,v,total+v)),3) | $3,540 | The running total after March |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Month | Sales | Running total | Best so far | ||
| Jan | $1,200 | $1,200 | $1,200 | $3,540 | |
| Feb | $1,350 | $2,550 | $1,350 | ||
| Mar | $990 | $3,540 | $1,350 | ||
| Apr | $1,500 | $5,040 | $1,500 |
Practice SCAN
Graded instantly, then re-checked on changed data.
Running total in one formula
C2, use SCAN to return a running total of the sales in B2:B9, one value per row.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| #CALC! | The LAMBDA returns an array instead of one value. |
| The first running total is off | Start initial_value at 0 for running totals. |
Tips
- A running balance: =SCAN(opening,B2:B20,LAMBDA(bal,v,bal+v)) with deposits positive and payments negative.
Questions
How do I make a running total with one formula?
=SCAN(0,B2:B100,LAMBDA(total,v,total+v)) spills a cumulative sum down the column.
Does SCAN work with text?
Yes, the LAMBDA can return text, such as joining values as it goes.
Lessons that use SCAN
Updated Checked by running: every formula on this page was run by our formula engine on .