I have 5 columns A to E.
Col A: Date - the data is sorted on this column
Col B: the financial year for the date in Col A
Col C: Elapsed time (hours and minutes, decimal)
Col D: a running aggregate for the whole of the data in Col A:
Here's what I need: a running aggregate in Col E, but one that resets at the start of a new financial year. I need a formula in Cell E2 that can be copied down. 🙂
See the attached example sheet - I have used different colours just for clarity.
In E2, enter:
=SUMIF($B$2:B2,B2,$C$2:C2)
and copy down.
Thanks Riny, I thought it would be a SUMIF but couldn't get it to work 🙂
The next step is to draw out the totals per FY, together with the FY value (cells H18:I20 in the attached). My guess is some sort of INDEX/MATCH but can't get it to work 🙁
Consider a pivot table as demonstrated in the attached file. Or, if you are using a moder Excel version you could use UNIQUE and FILTER.
Thanks Riny, I'm using Excel 2010 so the pivot table will have to do. Thanks!
