How to Calculate an Excel Running Total That Resets by Financial Year
Question details
The user wants to calculate a running total per person and month that automatically restarts at the beginning of a new calendar or financial year.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking cumulative totals (such as sales or hours) over time where the accumulation needs to reset conditionally based on a specific financial year threshold.
- Observed behavior
- The goal is to output a continuous sum that correctly groups data by person and resets the total back to zero when the month crosses into a new financial year.
Ensure your data is organized in a tabular format with dedicated columns for Name, Year, Month, and the Value to be summed. Verify that your Year and Month columns contain numeric values rather than text.
Use SUMPRODUCT for an Automatic Financial Year Reset
Use a dynamic SUMPRODUCT array formula to calculate running totals that check for the person's name and shift the grouping based on the financial year's starting month.
This formula assumes your data is structured with Name in column A, Year in column B (e.g., 22 or 2022), Month in column C (1-12), and the Value in column D.
The formula calculates a rolling sum by expanding the range ($2:2) as you drag it down, verifying the person's name matches and dynamically grouping the financial year.
Click on the first cell in your Running Total column (e.g., E2) adjacent to your first row of data.
Type the following formula: =SUMPRODUCT((D$2:D2)*(A$2:A2=A2)*((2000+B$2:B2+(C$2:C2>=4))=(2000+B2+(C2>=4)))). In this example, '4' represents April, the start of the financial year.
Press Enter to calculate the first result. Then, click and drag the fill handle at the bottom-right of the cell down to apply this formula to the rest of your dataset.
Use a Helper Column with SUMIFS
If you prefer a simpler formula, you can create a 'Financial Year' helper column first, and then use the standard SUMIFS function to calculate the running total.
Calculate Complex Running Totals Easily with WPS Office
WPS Spreadsheet provides robust support for advanced array formulas like SUMPRODUCT and SUMIFS, allowing you to seamlessly calculate conditional running totals. It is highly compatible with Excel, ensuring your financial reports work flawlessly without any syntax changes.
- 1. Open your data file: Launch WPS Spreadsheet and open the document containing your financial tracking data.
- 2. Select the output cell: Click on the cell where you want the running total to appear.
- 3. Input the formula: Paste the SUMPRODUCT or SUMIFS formula directly into the formula bar and press Enter.
- 4. Drag to fill: Use the fill handle to apply the running total logic across all rows in your financial report.
Frequently Asked Questions
Why is my SUMPRODUCT running total formula returning an error?
This usually happens if the expanding ranges in your formula do not align perfectly or if there is text in your value column. Ensure that your locked ranges (like $D$2:D2) start at the correct row and that your Name, Year, Month, and Value columns contain the correct data types.
Can I use a Pivot Table to calculate a running total by financial year?
Yes. You can create a Pivot Table and set the Value Field Settings to 'Show Values As' -> 'Running Total In'. However, to group by a custom financial year instead of a standard calendar year, you will still need to add a 'Financial Year' helper column to your source data first.
How do I adjust the formula if my financial year starts in July?
In the provided SUMPRODUCT formula, locate the logical test checking the month (C$2:C2>=4). Change the 4 to 7 to represent July. The formula will automatically adjust and reset the total every time the month reaches July.
Does the data need to be sorted by date for this formula to work?
The formula uses row-relative expanding ranges (like $D$2:D2) to evaluate data up to the current row. It will maintain an accurate running total based on the physical order of the rows, regardless of how the overall dataset is sorted globally.
