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.
Updated Checked by running: every formula on this page was run by our formula engine on .