If you can satisfy the below two criteria, it’s easy to code an array formula to reset the running total at every year change in Google Sheets.

What are those two conditions?

1. You must sort the table based on the date column in ascending or descending order.
2. No blank cells should be present in the date column.

Similar to our conditional running total, we can use MMULT here also.

But, as you may know, that’s a resource-hungry formula and may break when the data is large.

To reset the running total at every year change, we can use a SUMIF-based formula in Google Sheets. It seems to be a resource-friendly formula.

Sample Data:

The first two columns in the below table contain the sample data, and the third column contains our expected formula response.

Let’s code the array formula that resets the running total at every year change in Google Sheets.

Reset Running Total at Every Year Change (Array Formula)

The first two columns of the sample data are in A1:B, where A1 and B1 contain the field labels Date and Amount.

That means we require the calculation from row # 2, so the (array) formula should go in cell C2.

We have the data sorted in A-Z, aka ascending order. So we can use the following formula in cell C2 to reset the running total at every year change in A2:A.

For A-Z (Asc) Sorted Dates:

=ArrayFormula(if(len(A2:A),sumif(row(B2:B), "<="&row(B2:B), B2:B)-sumif(year(A2:A), "<"&year(A2:A), B2:B),))

Let me answer two quick questions that may arise in a user’s mind.

Does it include future records after row # 9?

Yep! It will.

Why does the formula return the #REF error?

If that’s the case, select C3:C and hit the delete button.

Because the formula in use to reset the running total at every year change is an array one.

It requires blank cells below cell C2 to expand down. Otherwise, it will try to overwrite the existing data and may cause the above-said error.

If the dates in column A are in Z-A (descending) order, the above running total restarts at every year change formula won’t work correctly.

You must make one change in the formula, i.e., replace < with >. Here it is!

For Z-A (Desc) Sorted Dates:

=ArrayFormula(if(len(A2:A),sumif(row(B2:B), "<="&row(B2:B), B2:B)-sumif(year(A2:A), ">"&year(A2:A), B2:B),))

Formula Explanation

Let’s consider the A-Z sorted data and formula for the explanation. That would help you understand the other one, i.e., data sorted in Z-A order, also.

Actually, there are two main parts to the formula, and here are they (highlighted in Red and Green).

=ArrayFormula(
     if(len(A2:A),
        sumif(row(B2:B), "<="&row(B2:B), B2:B)-
        sumif(year(A2:A), "<"&year(A2:A), B2:B),
     )
)

They return running totals (CUSUM) of column B in two different ways. I’ll come to that.

Part 1 (refer to Column E on the image below):

Sum column B if row numbers are less than or equal to row numbers. So it returns the summation of a sequence of numbers in A2:A at each row change.

=ArrayFormula(sumif(row(B2:B), "<="&row(B2:B), B2:B))

Please note that we require the ArrayFormula function to expand the result. So we must use it with each part as above when testing.

Part 2 (refer to Column F on the image below):

Sum column B if years are less than years. So it returns the summation of a sequence of numbers in B2:B at each year change in column A.

=ArrayFormula(sumif(year(A2:A), "<"&year(A2:A), B2:B))

Part 1 – Part 2 = final formula that resets the running total based on year change in the date column.

We have also used if(len(A2:A) to remove values from blank rows after row # 9.

The above are the resource-friendly array formulas to reset the running total at every year change in Google Sheets.

Thanks for the stay. Enjoy!

Resources

1. Reverse Running Total in Google Sheets (Array Formula).
2. Running Count in Google Sheets – Formula Examples.
3. How to Calculate Running Balance in Google Sheets.
4. Running Max Values in Google Sheets (Array Formula Included).
5. Find the Running Minimum Value in Google Sheets.
6. Calculating Running Average in Google Sheets (Array Formula).