The SCAN function in Google Sheets is a versatile tool that applies a custom LAMBDA formula to each element of an array, producing a series of intermediate results. This makes it perfect for tasks such as calculating running totals, creating cumulative paths, and other step-by-step calculations

Understanding the SCAN Function

The SCAN function processes each element in a one- or two-dimensional array and returns an array of the same size.

In the case of a 2D array, it works through all the values sequentially, applying the LAMBDA to the accumulated result and the current item.

While you can design your LAMBDA to do row-wise or column-wise calculations, SCAN itself loops through every element in order—it’s inherently element-wise.

Syntax

SCAN(initial_value, array_or_range, LAMBDA(accumulator, current_value, formula_expression))

Arguments:

• initial_value: The starting value of the accumulator. Its type depends on whether the array_or_range contains numeric or text values.
• array_or_range: The numeric or text array or range to be scanned.
• LAMBDA: A LAMBDA function that takes exactly two parameters:
  • accumulator: The accumulated value from the previous step.
  • current_value: The current element from the array or range.

SCAN Function with Numeric Data

The simplest use of the SCAN function is to calculate running totals (also known as cumulative sums or CUSUM).

Example: Running Total with a One-Dimensional Array

Assume you have the following values in cells A1:A5:

5
10
1
0
4

To compute the running total, enter the following formula in cell B1:

=SCAN(0, A1:A5, LAMBDA(acc, val, acc + val))

Result:

5
15
16
16
20

Explanation:

• Row 1: 0 (initial value) + 5 = 5
• Row 2: 5 (previous total) + 10 = 15
• Row 3: 15 + 1 = 16
• Row 4: 16 + 0 = 16
• Row 5: 16 + 4 = 20

Pro Tip:

When using open-ended ranges like A1:A, the SCAN function may produce unexpected results due to trailing empty cells. To limit the range dynamically to the last non-empty cell, use:

=SCAN(0, INDEX(A1:XLOOKUP(TRUE, A1:A<>"", A1:A, ,0, -1)), LAMBDA(acc, val, acc + val))

Example: Running Total with a Two-Dimensional Array

Consider the following values in cells A1:B5:

To compute the running total across the array, use:

=SCAN(0, A1:B5, LAMBDA(acc, val, acc + val))

Result:

Explanation:

SCAN processes A1:B5 element by element (A1, B1, A2, B2, etc.), adding each value to the running total (starting at 0). The result is an array showing the cumulative sum at each position.

SCAN Function with Text Data

The SCAN function can also be used with text values to build cumulative paths, such as file directory structures.

Example: Building File Paths

Assume you have the following folder structure in cells A2:A9:

C:
Users
John
Documents
D:
Projects
2025
Designs

To construct the full file paths, enter the following formula in cell B2:

=SCAN("", A2:A9, LAMBDA(acc, val, IF(RIGHT(val,1)=":", val, acc & "\" & val)))

Result:

C:
C:\Users
C:\Users\John
C:\Users\John\Documents
D:
D:\Projects
D:\Projects\2025
D:\Projects\2025\Designs

Explanation:

• If the current value ends with a colon (:), it indicates a new drive, and the accumulator resets to that value.
• Otherwise, the current value is appended to the accumulator with a backslash (\).

Dynamic Range Tip:

To handle dynamic ranges, replace A2:A9 with:

INDEX(A2:XLOOKUP(TRUE, A2:A<>"", A2:A, ,0, -1))

Advanced Use Case: Resetting Running Total at a Threshold

You can modify the SCAN function to reset the running total when a certain threshold is reached.

Example: Resetting at Threshold of 10

Assume you have values in cells B2:B16. To reset the running total when it reaches or exceeds 10:

=SCAN(0, B2:B16, LAMBDA(acc, val, IF(acc >= 10, val, acc + val)))

Explanation:

• If the accumulated value (acc) is greater than or equal to 10, the accumulator resets to the current value (val).
• Otherwise, it continues accumulating.

FAQ

Q: Can I use the SCAN function in Excel?

A: Yes, the SCAN function is available in Excel 2021 and Excel 365. It’s part of the LAMBDA helper functions and behaves similarly to the Google Sheets version. You can use it to compute running totals, cumulative paths, and other transformations by applying a custom LAMBDA expression across a range.

Q: Why does the SCAN function require exactly two parameters in the LAMBDA function?

A: The SCAN function applies the LAMBDA function to each element in the array, using the accumulator and the current value. Therefore, the LAMBDA function must have exactly two parameters: one for the accumulator and one for the current value.

Q: Can I use the SCAN function with text values?

A: Yes, the SCAN function can be used with text values to build cumulative strings, such as file paths or category trails.

Q: How can I handle dynamic ranges with the SCAN function?

A: To handle dynamic ranges, use the INDEX and XLOOKUP functions to define the range up to the last non-empty cell. For example:

INDEX(range_start:XLOOKUP(TRUE, range<>"", range, ,0, -1))

By understanding and utilizing the SCAN function in Google Sheets, you can perform complex cumulative operations with ease and efficiency.

Additional Resources

• How to Calculate Running Balance in Google Sheets (SUMIF and SCAN Solutions)
• Reset SCAN by Another Column in Google Sheets and Excel