The SCAN function is one of the LAMBDA helper functions in Google Sheets that returns an array result by applying a custom LAMBDA formula.

It scans an array or range by applying a LAMBDA formula to each value by moving row by row.

It produces an array that has each intermediate value.

Can you give an example of how this new function works in real-life scenarios in Google Sheets?

With the help of the SCAN function, we can easily get the running total of an array of values in Google Sheets.

The best thing is that we can reset the running total when it reaches a certain threshold or a specific value in the scan range or array, for example, at every blank cell.

We can even reset running totals at month, year, or category changes based on a helper column. We will discuss this part in later tutorials.

Related: Reset Running Total at Every Year Change in Google Sheets (SUMIF Based).

It might give the false impression that the SCAN function is only for cumulative sum. It’s not so.

Syntax of the SCAN Function in Google Sheets

Syntax:

SCAN(initial_value, array_or_range, LAMBDA)

Arguments:

initial_value – The starting value of the accumulator.

array_or_range – An array or range to be scanned.

LAMBDA – A LAMBDA formula that takes two name arguments to scan the array_or_range.

LAMBDA Syntax: =LAMBDA([name, ...],formula_expression)(function_call, ...)

Note: The function_call is only required in standalone LAMBDA use. Please check my Google Sheets function guide to learn this function.

Here, for the SCAN function, we can read the syntax as LAMBDA(name1, name2, formula_expression).

Where name1 is the current value in the accumulator and name2 is the current value in the array_or_range.

How to Use the SCAN Function in Google Sheets

Unlike the BYROW and BYCOL LHFs, we must straightaway code a SCAN formula, not a standalone LAMBDA formula, to understand the usage.

I’ve included four easy-to-follow examples below to help you master the SCAN function in Google Sheets.

With Text

1. B1 Formula (Join Characters):

=scan("✻",A1:A3,lambda(a,v,(a&v)))

Where;

initial_value – “✻”

Feel free to use any character(s) within double quotes or blank, i.e., "", since we are working with text.

array_or_range – A1:A3

LAMBDA – lambda(a,v,(a&v)) where a is the current value in the accumulator and v is the current value in A1:A3.

The accumulator a is updated in each step to the intermediate value obtained in the last/previous step.

2. C6 Formula (Join Characters):

I assume the following second SCAN formula in cell C6 is self-explanatory.

=scan("✻",A6:B7,lambda(a,v,(a&v)))

Below you can find the use of the SCAN function with numbers in an array in Google Sheets.

With Numbers

3. B11 Formula (Running Total):

=scan(0,A11:A14,lambda(a,v,(a+v)))

Where;

initial_value – 0

The array/range A11:A14 contain numbers, and the LAMBDA is for performing an addition operation. So we must use 0 (zero) or any number in the initial_value.

array_or_range – A11:A14

LAMBDA – lambda(a,v,(a+v)) where a is the current value in the accumulator and v is the current value in A11:A14.

As earlier, the accumulator a is updated in each step to the intermediate value obtained in the last/previous step.

4. B18 Formula (Running Total):

=scan(100,A18:A21,lambda(a,v,(a+v)))

In this SCAN formula, I’ve set the initial_value to 100. That’s the only difference compared to the B11 formula here.

Scan Function Advanced Examples in Google Sheets

You can find below three clever use of the SCAN function in Google Sheets.

1. Reset Running Total Reaching Certain Threshold

We have already seen how to use the SCAN function for running total in Google Sheets. Please scroll up and see example # 3.

To reset it at reaching a certain threshold, say 10, we can use an IF Logical test with the name1 (accumulator) in the formula_expression part.

Instead of lambda(a,v,(a+v)) given in the previous example, use lambda(a,v,(if(a>=10,v,a+v))).

=scan(0,B2:B16,lambda(a,v,(if(a>=10,v,a+v))))

Note:- In the above example, the array_or_range is vertical data, and so is the output. If it’s horizontal, the returned values will also be horizontally aligned.

2. Reset Running Total at Blank Cells

If you want your SCAN formula to reset the running total at a specific row, insert a blank row and use the below formula.

By slightly modifying the IF logical part of the above formula, we can reset the SCAN formula result at blanks instead of reaching the threshold.

Just replace v>=10 with v="" and voila!

=scan(0,B2:B16,lambda(a,v,(if(v="",v,v+a))))

3. How to Use SCAN Function for Group Wise Count

Assume, we have country names in cell range A1:A6 in the order UK, USA, USA, UAE, UAE, and UAE.

So we expect UK = 1, USA = 2, UAE = 3, i.e., the count of occurrence of country names in the range.

We can actually use a UNIQUE and COUNTIF combination to get the summary as above.

For that enter =unique(A1:A6) in cell C1 and =ArrayFormula(countif(A1:A6,F1:F3)) in cell D1.

We can use the SCAN function in Google Sheets to replace the above COUNTIF formula.

In cell D1 insert =ArrayFormula(SCAN(0,unique(A1:A6),LAMBDA(a,v,SUM((v=A1:A6)*1)))).

And the above use case is just to make you understand the potential of the SCAN function in Google Sheets.

Does the above SCAN formula works with numbers and also with unsorted values?

Yep! There is no such issue found.