In this guide, we’ll explore the step-by-step process to reset the running total at blank rows in Google Sheets.

Assume you have a dataset where you create sections by inserting one or more blank rows. Certainly, you can use multiple running total array formulas in the beginning row of each section for specific ranges.

However, our objective is to have a running total array formula in the topmost cell that populates the running total and resets when encountering blank rows, in other words, when a new section begins.

This approach is most useful when you don’t have a category column, instead separating each category by a blank row.

Running totals (cumulative sum) can be applied for a variety of purposes, such as monitoring expenses, income, sales, etc., enabling you to make informed decisions.

In this tutorial, you will learn about three running total reset formulas that can be applied in three distinct scenarios.

1. Single Column Data: The formula identifies blank cells in the column you’re using to calculate the cumulative sum.
2. Multiple Columns Data: The formula identifies blank cells based on values in a column other than the one used for the cumulative sum.
3. Multiple Columns Data: A row is considered blank if all cells in the row are empty.

Reset Running Total at Blank Cells in a Single Column in Google Sheets

In Google Sheets, we can utilize the SCAN function for cumulative operations on arrays or ranges.

Assume you have sales figures in column B (B2:B) and want to create a running total reset formula in cell C2. You can achieve this using the following SCAN formula:

=SCAN(0, B2:B, LAMBDA(a, v, IF(v="",,a+v)))

This formula follows the syntax:

SCAN(initial_value, array_or_range, LAMBDA(name1, name2, formula_expression))

Where:

• initial_value: Set to 0.
• array_or_range: B2:B.
• name1: Represents the previous value of the running total, denoted as a. It functions as the accumulator for the cumulative sum.
• name2: Represents the value of the current cell in B2:B, denoted as v.
• formula_expression: The formula, i.e., IF(v="",,a+v), checks if the current value (v) is blank. If true, it returns an empty value; otherwise, it adds the current value (v) to the running total (a)

Essentially, the formula resets the running total accumulator value when a blank cell is encountered in the array_or_range.

Additionally, the SCAN formula returns the intermediate value in the accumulator (a) at each step within the rows of the specified range.

Reset Running Total at Blank Rows in Multiple Columns Data

In the example above, we had only one column, and the determination of blank rows was based on that column.

However, when dealing with multiple columns of data, you might want to consider blank rows across all columns or a specific column.

Specific Column

In the following example, the data range is A2:B, and we want to calculate the cumulative sum of B2:B, resetting this running total when a blank cell is encountered in column A.

Insert the following SCAN formula in cell C2:

=SCAN(0, B2:B, LAMBDA(a, v, IF(OFFSET(v,0,-1)="",,a+v)))

This formula is similar to our single-column formula but with the inclusion of the OFFSET function.

Syntax of the OFFSET Function:

OFFSET(cell_reference, offset_rows, offset_columns, [height], [width])

In this context, the OFFSET function offsets 0 rows and -1 column, returning the value in the current row of column A for the IF logical test. If that value is blank (" "), the formula returns blank; otherwise, it adds the current value to the running total.

Throughout each row, the formula will return the intermediate value stored in a.

In summary, the combination of SCAN and OFFSET resets the running total when encountering a blank cell in column A.

Across All Columns

Sometimes, the determination of blank rows involves more than one column. In such cases, the running total reset formulas mentioned above, which rely on a single column as the blank row identifier, may not be suitable.

Consider the following example where there are three columns in the dataset, and you want the formula to reset if all columns are empty.

Formula:

=SCAN(0, C2:C, LAMBDA(a, v, IF(COUNTA(OFFSET(v, 0, -2, , 3))>0, a+v,)))

This formula resets the running total in completely empty rows.

The formula_expression is as follows: IF(COUNTA(OFFSET(v, 0, -2, , 3)) > 0, a + v, )

In each row, the OFFSET function offsets -2 columns from column C and returns values from columns A, B, and C, with an offset width of 3.

The COUNTA function returns the count of values in these cells. If that count is greater than 0, it adds the current value to the running total; otherwise, it returns blank.

This approach allows you to reset the running total in blank rows in Google Sheets.