Search code examples
excelexcel-formulaoffice365

Running total that restarts every fiscal year (Oct - Sep)

Example of my desired result:

Date (A) Value (B) Fiscal Year running total (C)
01.09.2022 100 100
01.10.2022 200 200
01.11.2022 50 250
01.12.2022 150 400
01.01.2023 125 525
01.02.2023 300 825
01.03.2023 250 1075
01.04.2023 300 1375
01.05.2023 100 1475
01.06.2023 75 1550
01.07.2023 125 1675
01.08.2023 150 1825
01.09.2023 100 1925
01.10.2023 25 25
01.11.2023 300 325
01.12.2023 125 450

I would like to know how I can achieve the 3rd column in this example table, "Fiscal Year running total". Meaning, I'd like to have a running total that resets at the start of every fiscal year. In this case, fiscal years starts in October and ends in September.

Another way of wording it:
I need a way of getting the running total from an unspecific date, to the last time it was a specific date (October).

If it is at all possible, it'd be great to have a formula instead of VB or Power Query solution.

Here is a formula I have come up with, but not found a full solution for yet:

=IF (MONTH (A2) = 10; B2; SUM (OFFSET (INDEX (MATCH (???); 0): A2; 0; 1))

"(???)" Being where one would need a formula to find the cell closest to the one we are in that has October in its month and is in the past.

Many thanks in advance!

Solution

  • To be honest, @JvdV answer is a shorter formula but just in case somebody can't use Excel365 here's another approach using SUMPRODUCT:

    enter image description here

    =SUMPRODUCT(--($A$2:$A$17<=A2)*--($A$2:$A$17>=IF(MONTH(A2)>=10;DATE(YEAR(A2);10;1);DATE(YEAR(A2)-1;10;1)))*--($A$2:$A$17<=IF(MONTH(A2)>=10;DATE(YEAR(A2)+1;9;30);DATE(YEAR(A2);9;30)))*$B$2:$B$17)

    Notice this formula will work only if dates in column A are real dates (numeric) and not text dates.