Can SCAN be used to calculate a running total across multiple columns at once?

Yes, but you need to collapse the columns into a single array first, for example with MAKEARRAY or BYCOL, and then feed that one-dimensional array to SCAN. SCAN itself only processes a single linear sequence.

Why does SCAN return a vertical spill even when my source array is horizontal?

SCAN always returns a one-dimensional array oriented vertically. If you need a horizontal result, wrap the SCAN call in TRANSPOSE to flip the orientation.

What’s the difference between SCAN and REDUCE?

SCAN returns every intermediate accumulator value, producing a spill array, whereas REDUCE returns only the final accumulator after processing the whole input. Choose SCAN for running totals and REDUCE for a single summary value.

How can I ignore blank rows in my budget table when using SCAN?

Filter the source array first, for example with FILTER(INDEX(data,,3), INDEX(data,,3)<>""), and then pass the filtered result to SCAN. Blank rows that produce empty values would otherwise be treated as zeros.

SCAN function

SCAN returns a spill array of accumulated results by applying a custom LAMBDA to each element of an input array, starting from an initial value.

=SCAN(initial_value, array, lambda)

Generate a SCAN formula

Describe what you need. The generator will reach for SCAN where SCAN is the right tool, and tell you when it is not.

Arguments

How SCAN reads its arguments

Argument	Required	Description
initial_value	Required	The starting accumulator; can be any scalar, array, or reference – it becomes the first value fed into the lambda.
array	Required	A single-column or single-row array (or any range) whose elements are processed in order; empty arrays produce a single-cell result of the initial value.
lambda	Required	A LAMBDA that must accept exactly two parameters – the accumulated value and the next array element – and return the new accumulator; mismatched return shapes cause errors.

Returns

It returns a one-dimensional spill array whose length equals the input array’s length.

Availability

Excel: 365 · Google Sheets: Supported

Worked examples

1. Cumulative budgeted totals by category

Category	Month	Budgeted	Actual	Variance
Groceries	Jan	400	450	50
Rent	Jan	1200	1200	0
Utilities	Jan	150	130	-20
Entertainment	Jan	100	150	50

=LET(data,{ {"Category","Month","Budgeted","Actual","Variance"};{"Groceries","Jan",400,450,50};{"Rent","Jan",1200,1200,0};{"Utilities","Jan",150,130,-20};{"Entertainment","Jan",100,150,50} }, SCAN(0, INDEX(data,,3), LAMBDA(acc,val, acc+val) ) )

Result: {400;1600;1750;1850}

The formula extracts the third column (Budgeted) from the literal dataset, then SCAN starts at 0. For each row the lambda adds the current budgeted amount to the running total, producing 400 after the first row, 1600 after adding 1200, 1750 after adding 150, and finally 1850 after the last 100. The result spills vertically as a single column.

2. Running net variance (budgeted vs actual)

Category	Month	Budgeted	Actual	Variance
Groceries	Jan	400	450	50
Rent	Jan	1200	1200	0
Utilities	Jan	150	130	-20
Entertainment	Jan	100	150	50

=LET(data,{ {"Category","Month","Budgeted","Actual","Variance"};{"Groceries","Jan",400,450,50};{"Rent","Jan",1200,1200,0};{"Utilities","Jan",150,130,-20};{"Entertainment","Jan",100,150,50} }, SCAN(0, INDEX(data,,5), LAMBDA(acc,val, acc+val) ) )

Result: {50;50;30;80}

Here the fifth column (Variance) is fed to SCAN. Starting at 0, the lambda adds each variance to the accumulator. After the first row the net variance is 50, stays 50 after the second row (0 added), drops to 30 after the -20 from Utilities, and rises to 80 after the final +50 from Entertainment. The output shows the cumulative over-/under-budget position after each category.

3. Running cash balance after each expense

Category	Month	Budgeted	Actual	Variance
Groceries	Jan	400	450	50
Rent	Jan	1200	1200	0
Utilities	Jan	150	130	-20
Entertainment	Jan	100	150	50

=LET(data,{ {"Category","Month","Budgeted","Actual","Variance"};{"Groceries","Jan",400,450,50};{"Rent","Jan",1200,1200,0};{"Utilities","Jan",150,130,-20};{"Entertainment","Jan",100,150,50} }, SCAN(2000, INDEX(data,,4), LAMBDA(bal,expense, bal-expense) ) )

Result: {1550;350;220;70}

The fourth column (Actual) represents cash outflows. Starting with an initial balance of 2000, the lambda subtracts each expense. After Groceries the balance falls to 1550, after Rent to 350, after Utilities to 220, and after Entertainment to 70. The spill array therefore tracks the remaining cash after each line item.

Common errors

Which SCAN error are you seeing?

Error	Why it happens	How to fix it
#SPILL!	The SCAN result tries to spill into cells that already contain data, blocking the dynamic array.	Move the formula to an empty area or clear the obstructing cells so the spill range can expand.
#VALUE!	The lambda returns an array (e.g., LAMBDA(a,b,{a,b})) instead of a single scalar, breaking the accumulator contract.	Ensure the lambda returns a single value of the same type as the accumulator; remove the extra braces or use a function that reduces to a scalar.
#REF!	The array argument references a range that no longer exists, such as a deleted column or an invalid named range.	Correct the reference so it points to a valid range or rename the named range to match the current workbook layout.

Tips and when to use something else

Use LET to store the raw budget table once and reference its columns multiple times – it keeps formulas readable and fast.
If you only need the final accumulated value (e.g., total budget), REDUCE is more efficient because it returns a single result instead of a full spill array.
When the accumulator should stay a scalar but you accidentally return a range, wrap the lambda result in INDEX(...,1,1) to coerce a single cell.
For two-dimensional cumulative calculations (e.g., running totals across rows and columns), combine SCAN with BYROW or BYCOL rather than trying to nest SCAN directly.

Related functions

LETLET lets you name a single intermediate result inside a formula, then return that result, making complex calculations clearer and easier to read.LAMBDALAMBDA lets you create custom, reusable functions directly in a cell, returning any value or array based on supplied parameters.REDUCEREDUCE iteratively applies a LAMBDA to each element of an array, carrying forward an accumulator that starts with the initial value.BYROWBYROW applies a LAMBDA to each row of a range and returns a vertical array of the results, enabling row-wise calculations in Excel 365 and Google Sheets.MAPMAP applies a custom LAMBDA to each element of a range, returning a new array of the same shape with the calculated results.

Frequently asked questions

Can SCAN be used to calculate a running total across multiple columns at once?: Yes, but you need to collapse the columns into a single array first, for example with MAKEARRAY or BYCOL, and then feed that one-dimensional array to SCAN. SCAN itself only processes a single linear sequence.
Why does SCAN return a vertical spill even when my source array is horizontal?: SCAN always returns a one-dimensional array oriented vertically. If you need a horizontal result, wrap the SCAN call in TRANSPOSE to flip the orientation.
What’s the difference between SCAN and REDUCE?: SCAN returns every intermediate accumulator value, producing a spill array, whereas REDUCE returns only the final accumulator after processing the whole input. Choose SCAN for running totals and REDUCE for a single summary value.
How can I ignore blank rows in my budget table when using SCAN?: Filter the source array first, for example with FILTER(INDEX(data,,3), INDEX(data,,3)<>""), and then pass the filtered result to SCAN. Blank rows that produce empty values would otherwise be treated as zeros.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17