Resetting SCAN function results based on values in another column is a topic of increasing interest.

Although the SCAN function can technically process a two-dimensional array, it iterates through the array row by row, not element by element. This means it processes each row as a whole, returning cumulative results across the entire row for each column, rather than independently for each column.

As a result, it’s not suitable for resetting running totals based on values in a different column, as it will return cumulative results across all columns.

Syntax:

SCAN(initial_value, array_or_range, lambda) – Google Sheets
SCAN(initial_value, array, function) – Excel

You can reset SCAN-based running totals by another column (such as a group column) using the OFFSET function in Excel and Google Sheets. However, OFFSET requires physical ranges and may not work when you’re dealing with virtual arrays returned by formulas like FILTER or SORT.

Fortunately, there’s a more flexible formula approach that works identically in both Excel and Google Sheets. Below, we’ll look at two key use cases:

1. Reset SCAN running total by a specific value in a column (e.g., “Sunday” or a blank).
2. Reset SCAN running total by a group column (e.g., Category, Month, Year).

Reset SCAN Running Total by a Specific Value in a Column

Sample Data (A1:B15):

To reset the SCAN running total at every “Sun”, try the following formulas:

Using OFFSET:

=SCAN(0, B2:B15, LAMBDA(acc, val,
   IF(OFFSET(val, 0, -1) = "Sun", val, acc + val)
))

• IF(OFFSET(val, 0, -1) = "Sun", val, acc + val) resets when column A has “Sun”.

Pros: Simple to understand.
Cons: Not compatible with virtual ranges.

Without OFFSET:

=SCAN(0, SEQUENCE(ROWS(B2:B15)), LAMBDA(acc, val,
   LET(capture,
      LAMBDA(col, INDEX(col, val)),
      col_1, capture(A2:A15),
      col_2, capture(B2:B15),
      IF(col_1 = "Sun", col_2, acc + col_2)
   )
))

How it Works:

• SEQUENCE generates row indices.
• capture pulls a specific row value from a column.
• Logic resets total at “Sun”; otherwise, it accumulates.

Pros: Works with expressions like CHOOSECOLS, FILTER, etc.
Cons: Slightly more complex.

Reset SCAN Running Total by a Group Column

Sample Data (A2:C7):

Goal: Reset the SCAN total when the Category (group column) changes.

Using OFFSET:

=SCAN(C2, C2:C7, LAMBDA(acc, val,
   LET(
      p_cat, OFFSET(val, -1, -1),
      c_cat, OFFSET(val, 0, -1),
      IF(c_cat <> p_cat, val, acc + val)
   )
))

• p_cat = previous category
• c_cat = current category
• Reset when the group changes.

Without OFFSET:

=SCAN(0, SEQUENCE(ROWS(C2:C7)), LAMBDA(acc, val,
   LET(
      capture, LAMBDA(col, INDEX(col, val)),
      capturePv, LAMBDA(col, CHOOSEROWS(INDEX(col, val-1), 1)),
      col_1, capture(B2:B7),
      col_2, capture(C2:C7),
      col_1Pv, capturePv(B2:B7),
      IF(col_1 <> col_1Pv, col_2, acc + col_2)
   )
))

Explanation:

• Resets when the category in column B changes.
• Works for any kind of grouping: category, year, month, etc.

Pros and Cons:
These share the same pros and cons as the previous examples. OFFSET-based formulas are simple but limited to static ranges. Non-OFFSET versions are powerful and dynamic but more advanced.

Conclusion

These SCAN-based formulas are powerful tools in both Excel and Google Sheets. You can reset SCAN results by category, specific values, or when encountering empty rows. Prefer the non-OFFSET approach when working with dynamic or formula-generated ranges.