Reset SCAN by Another Column in Google Sheets and Excel

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):

Running total resets at 'Sun' using SCAN

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):

Running total resets by category using SCAN

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.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Expert Prashanth KV is a Diamond Product Expert in Google Sheets, officially recognized by Google for his contributions to the Docs Editors Help Community and featured in the Google Product Experts Directory. With years of experience working with Google Sheets and Excel for multinational firms in Mumbai and Dubai, he has been blogging since 2012, offering practical, real-world spreadsheet solutions that professionals rely on. Explore his blog to learn advanced formulas, automation tips, and problem-solving techniques to elevate your spreadsheet skills.

Mode of Comma-Separated Numbers in Excel (Dynamic Array)

There is no dedicated function in Excel to directly find the mode of comma-separated...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

Count Consecutive Workday Absences in Google Sheets

This tutorial offers a powerful formula-based solution to count consecutive workday absences in Google...

More like this

How to Calculate Maximum Drawdown in Excel and Google Sheets

You can use the following dynamic array formula to calculate maximum drawdown (MDD) in...

Lookup Previous Values Dynamically in Excel and Google Sheets

Sometimes, you may want to look up the previous values relative to the current...

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.