If you’re looking to reset the running total at every year change in Google Sheets, there are two specific conditions you need to meet. First, you must sort the table based on the date column in either ascending or descending order. Second, there should be no blank cells in the date column.
In the past, you might have used the MMULT formula for conditional running totals, but that formula can be resource-intensive and may break when dealing with large data sets. Luckily, there’s a resource-friendly alternative – a SUMIF-based formula.
How to Reset Running Total at Every Year Change (Array Formula)
Let’s dive into the specifics. In your sample data, the first two columns contain the date and amount, while the third column represents the expected formula response.
To reset the running total at every year change, we’ll be using an array formula that you can find more details about on Crawlan.com. The formula goes as follows:
=ArrayFormula(if(len(A2:A),sumif(row(B2:B), "<="&row(B2:B), B2:B)-sumif(year(A2:A), "<"&year(A2:A), B2:B),))The formula should be placed in cell C2, as the calculation is based on the data starting from row 2. If your data is sorted in ascending order, you can use the above formula as is. However, if your data is sorted in descending order, you’ll need to replace the “<” with “>” in the formula.
It’s worth noting that the above formula is an array formula, which means you may encounter a #REF error if there are no blank cells below cell C2. To avoid this, simply select cells C3:C and hit the delete button to make room for the formula to expand.
Formula Explanation
Let’s break down the formula into its two main parts, 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), ) )Part 1 (Column E) calculates the sum of column B for row numbers that are less than or equal to the current row number. This gives us the summation of a sequence of numbers in column A for each row change.
Part 2 (Column F) calculates the sum of column B for years that are less than the current year. This gives us the summation of a sequence of numbers in column B for each year change in column A.
By subtracting Part 2 from Part 1, we achieve the final formula that resets the running total based on year changes in the date column.
That’s it! You now have a resource-friendly array formula that resets the running total at every year change in Google Sheets. Enjoy!
Resources
- Reverse Running Total in Google Sheets (Array Formula)
- Running Count in Google Sheets – Formula Examples
- How to Calculate Running Balance in Google Sheets
- Running Max Values in Google Sheets (Array Formula Included)
- Find the Running Minimum Value in Google Sheets
- Calculating Running Average in Google Sheets (Array Formula)
