How to Reset Running Totals on Multiple Conditions in Google Sheets (SCAN + LAMBDA) (Step-by-Step)

How to Calculate Running Totals with Multiple Reset Conditions Using SCAN and LAMBDA in Google Sheets

When a running total needs to reset based on more than one condition, a normal cumulative SUM formula quickly becomes difficult to manage. I use SCAN and LAMBDA for this because they let you process each row in sequence and decide exactly when the total should restart.

30-SECOND SUMMARY

The Quick Answer

Suppose your data has:

• Column A: Employee
• Column B: Month
• Column C: Sales amount

You want a running total that resets whenever either the employee or month changes. A basic SCAN pattern looks like this:

=SCAN(0,A2:C10,LAMBDA(acc,row, IF( INDEX(row,1,1)&INDEX(row,1,2)<>previous_condition, INDEX(row,1,3), acc+INDEX(row,1,3) ) ))

In real spreadsheet work, the most reliable approach is to create a reset flag by comparing the current row with the previous row, then use that flag inside SCAN.

For example, if your reset condition is stored in column D as TRUE or FALSE:

=SCAN(0,C2:C10,LAMBDA(total,value, IF(INDEX(D2:D10,ROW(value)-ROW(C2)+1), value, total+value )))

However, because SCAN processes values sequentially, a cleaner practical solution is often to pass the amount and reset flag together as an array:

=SCAN(0,HSTACK(C2:C10,D2:D10), LAMBDA(total,row, IF(INDEX(row,1,2), INDEX(row,1,1), total+INDEX(row,1,1) )))

Important: Your data must be sorted in the order you want the running total calculated.

What Are We Trying to Solve?

Imagine you maintain a sales sheet for employees. You want to calculate a cumulative sales total, but the total must restart when:

• The employee changes, or
• The month changes.

Here is a realistic example:

Employee	Month	Sales	Reset?	Running Total
XYZ	January	120	Yes	120
XYZ	January	80	No	200
XYZ	January	150	No	350
XYZ	February	100	Yes	100
XYZ	February	90	No	190
ABC	February	200	Yes	200
ABC	February	50	No	250

The reset happens on row 1 because there is no previous record. It happens again when January becomes February, and again when XYZ becomes ABC.

The Anatomy of SCAN and LAMBDA

Before building the final formula, let me break down the two functions.

SCAN

=SCAN(initial_value,array,LAMBDA(accumulator,current_value,calculation))

Part	What It Does
initial_value	The starting value for the calculation, such as 0.
array	The data SCAN processes one item or row at a time.
LAMBDA	Your custom calculation rule.
accumulator	The running result from the previous step.
current_value	The current item being processed.

A very simple running total is:

=SCAN(0,C2:C10,LAMBDA(total,value,total+value))

If column C contains 120, 80, and 150, the result is:

120 → 200 → 350

That is useful, but it never resets. So we need to add reset logic.

Why Use LAMBDA?

LAMBDA lets you name the values used during each SCAN step. In this part:

LAMBDA(total,row, ... )

• total is the accumulated result so far.
• row represents the current row data.

You can then write a normal IF rule:

=IF(reset_condition,current_amount,total+current_amount)

That is the main idea behind a running total with resets.

Step-by-Step Practical Walkthrough

Let's build this using a sample sheet. Assume your columns are arranged like this:

A	B	C	D	E
Employee	Month	Sales	Reset Flag	Running Total
XYZ	January	120
XYZ	January	80
XYZ	January	150
XYZ	February	100
XYZ	February	90
ABC	February	200
ABC	February	50

Step 1: Create a Reset Flag

First, we need to identify when the current row belongs to a new group.

In D2, enter:

=TRUE

The first data row should always reset because there is no previous row.

Then, in D3, use:

=OR(A3<>A2,B3<>B2)

Copy the formula downward.

This means:

• If the employee changed, return TRUE.
• If the month changed, return TRUE.
• If neither changed, return FALSE.

Pro Tip: The operator <> means "not equal to." It is one of the simplest ways to detect a change between the current and previous row.

Step 2: Use SCAN to Build the Running Total

Now that column D tells us exactly when to reset, the SCAN formula becomes much easier to understand.

In E2, use:

=SCAN(0,HSTACK(C2:C8,D2:D8), LAMBDA(total,row, IF( INDEX(row,1,2)=TRUE, INDEX(row,1,1), total+INDEX(row,1,1) ) ))

Let's break down what happens on each row:

Current Row	Reset Flag	Calculation	Result
120	TRUE	Start with 120	120
80	FALSE	120 + 80	200
150	FALSE	200 + 150	350
100	TRUE	Reset to 100	100
90	FALSE	100 + 90	190
200	TRUE	Reset to 200	200

Step 3: Understand the HSTACK Part

This part often confuses beginners:

HSTACK(C2:C8,D2:D8)

It temporarily combines your Sales column and Reset Flag column into one two-column array.

Each SCAN step receives a row containing:

Current Amount | Reset Flag

For example:

120 | TRUE 80 | FALSE 150 | FALSE

Inside the LAMBDA:

• INDEX(row,1,1) gets the sales amount.
• INDEX(row,1,2) gets the reset flag.

A More Compact Approach Using Combined Conditions

You can also create a helper group key. This is useful when your reset depends on several columns.

For example, combine Employee and Month in column D:

=A2&"|"&B2

This creates values such as:

XYZ|January XYZ|January XYZ|February ABC|February

Then your reset formula can compare the current key with the previous key:

=D3<>D2

Pro Tip: Using a separator such as | helps avoid accidental combinations. For example, combining text without a separator can sometimes create ambiguous values.

What If You Have Three or More Reset Conditions?

The same method works. Suppose the running total should reset when any of these changes:

• Employee
• Month
• Project

Your reset flag can be:

=OR(A3<>A2,B3<>B2,C3<>C2)

Or, if you have many conditions, create a combined key:

=A2&"|"&B2&"|"&C2

Then compare each key with the previous one.

This is one of the reasons I prefer the helper-column approach for beginners. The SCAN formula stays simple even when the reset logic becomes more complex.

Excel vs. Google Sheets

The core idea is similar in both spreadsheet applications, but function availability and implementation can differ depending on your version.

Feature	Google Sheets	Excel
SCAN	Available in supported modern versions	Available in modern versions with dynamic array functions
LAMBDA	Used directly inside array formulas	Also supports named custom LAMBDA functions
Dynamic arrays	Results can spill automatically	Results can spill automatically
HSTACK	Useful for combining arrays horizontally	Available in modern versions
Older spreadsheet versions	May require traditional formulas	May require helper columns or older techniques

The biggest practical difference is often the spreadsheet version you are using. If SCAN or LAMBDA is not recognized, check whether your version supports those functions.

3 Common Mistakes and How to Fix Them

Mistake 1: Your Data Is Not Sorted

Problem: The employee or month appears in different places throughout the sheet, so the formula resets unexpectedly.

For example:

Employee	Month
XYZ	January
ABC	January
XYZ	January

The formula sees the second XYZ record as a new group because ABC appeared in between.

Fix: Sort the data by the columns that define your groups before calculating the running total.

Mistake 2: Hidden Extra Spaces Cause Unexpected Resets

Problem: One row contains XYZ, while another contains XYZ with an extra space.

They look identical, but the spreadsheet treats them as different text values.

Fix: Clean the comparison values with TRIM:

=OR(TRIM(A3)<>TRIM(A2),TRIM(B3)<>TRIM(B2))

If your source data is imported or copied from another system, this small cleanup step can save a lot of debugging time.

Mistake 3: Range Sizes Do Not Match

Problem: Your HSTACK formula combines ranges with different numbers of rows.

For example:

=HSTACK(C2:C10,D2:D9)

These ranges have different sizes, which can produce an error or unexpected results.

Fix: Always make sure every range inside HSTACK starts and ends on the same rows.

=HSTACK(C2:C10,D2:D10)

Another Beginner Mistake: Using a Circular Reference

A circular reference happens when your running-total formula directly or indirectly refers to its own output range.

For example, trying to make the formula in column E depend on the previous calculated value in column E can become difficult when you also want one spilled array formula.

Why SCAN helps: SCAN maintains the previous accumulated value internally through the total parameter. You do not need to manually reference the previous output cell.

Pro Tip: If you find yourself writing a formula that refers to the cell immediately above, stop and ask whether SCAN can manage that running state for you.

How to Make the Formula Dynamic for New Rows

In a real sales sheet, new records are added regularly. Instead of manually changing:

C2:C8

You may want to use an open-ended range:

C2:C

However, open-ended ranges can include blank rows. If your formula starts processing blanks as zeros, the output may extend farther than expected.

A cleaner approach is to filter blank rows first:

=FILTER(A2:C,A2:A<>"")

You can then build your SCAN logic around the filtered data.

Common Pitfall: If one of your grouping columns contains blanks in the middle of the data, do not automatically assume that the row should be ignored. Decide whether a blank is valid data or an incomplete record before filtering.

Formula Pattern You Can Reuse

For future spreadsheets, remember this pattern:

=SCAN( starting_value, HSTACK(amount_range,reset_flag_range), LAMBDA(accumulator,current_row, IF( reset_condition, current_amount, accumulator+current_amount ) ) )

Translated into actual logic:

=SCAN(0,HSTACK(amount_range,reset_flag_range), LAMBDA(total,row, IF( INDEX(row,1,2)=TRUE, INDEX(row,1,1), total+INDEX(row,1,1) )))

You only need to change:

• The amount range
• The reset flag range
• The reset conditions

Downloadable Example / Actionable Takeaway

COPY THIS WORKFLOW

Your 5-Step Setup

1. Create columns for the conditions that define each group.
2. Sort your data in the correct calculation order.
3. Create a Reset Flag by comparing the current row with the previous row.
4. Combine the amount and reset flag with HSTACK.
5. Use SCAN and LAMBDA to either restart or add to the previous total.

Starter formulas:

Reset flag:

=OR(A3<>A2,B3<>B2)

Running total:

=SCAN(0,HSTACK(C2:C,D2:D), LAMBDA(total,row, IF( INDEX(row,1,2)=TRUE, INDEX(row,1,1), total+INDEX(row,1,1) )))

Keyboard Shortcut Tip: When editing a long formula, use your spreadsheet's formula bar to review line breaks and parentheses. I also recommend testing the formula on 5–10 rows first before applying it to a large dataset.

When Should You Use SCAN Instead of a Normal Running SUM?

Use a normal running total when every row belongs to one continuous sequence. Use SCAN when the result depends on the previous calculated state and you need logic such as:

• Reset when a category changes
• Reset when a new employee appears
• Reset when a month changes
• Reset when a project changes
• Reset when any of several conditions changes
• Apply different logic based on the previous accumulated value

Once you understand the accumulator concept, SCAN becomes much easier to use. The formula is essentially reading your data from top to bottom and carrying the previous result forward.

FAQ: Running Totals with SCAN and LAMBDA

1. Can SCAN reset a running total based on multiple columns?

Yes. Create a reset condition that checks whether any relevant column has changed compared with the previous row. For example, use OR to check Employee and Month, then pass that reset flag into SCAN.

2. Why does my running total reset in the wrong place?

The most common reasons are unsorted data, hidden spaces, inconsistent text values, or an incorrect reset condition. Check your reset flag first. If the flag is correct, the SCAN result should usually be correct as well.

3. Do I need a helper column for the reset condition?

No, but I recommend one for beginners. A helper column makes it easy to see exactly where the formula thinks a new group starts. After testing, you can build the reset logic into a more compact formula if needed.

4. Can I reset the running total when three or more conditions change?

Yes. Add more comparisons inside OR, or create a combined group key from all columns that define a group. The SCAN formula itself does not need to change much.

Final Takeaway

The hardest part of a running total with multiple reset conditions is usually not SCAN itself. It is defining exactly when a new group starts.

My practical recommendation is simple: first build and verify a reset flag, then use SCAN to carry the running total forward. This approach is easier to test, easier to debug, and much easier to adapt when you later add another reset condition.

Once you are comfortable with SCAN and LAMBDA, you can use the same pattern for cumulative sales, student marks, project hours, task counts, inventory movement, and many other row-by-row calculations.