Calculating YTD, MTD for large dataset

[Estee28]

Hello,

I am attempting to calculate the YTD, MTD, CYTD and revenue to date for a very large data set which spans the past three years. I have included a small sample below and welcome suggestions on the best way to achieve this. Kind regards,

Esther

Year	Month	Banker	Segment	Product Type	Relationship name	Product name	Balances
2016	Nov						67771
2016	Dec						38881
2016	Dec						38401
2017	Jan						23011
2017	Jan						17625
2017	feb						45282
2017	feb						34627

<tbody>
</tbody>

 

[FDibbins]

1st, I would advise to use real dates, rather than just numbers (for the year) and text (for the mth), that way you can use SUMIFS() based on dates.

Otherwise, you could probably still use SUMIFS with something like...
YTD
=sumifs(H:H,A:A,2017)
where H contains the sum values and A contains the year

MTD
=sumifs(H:H,A:A,2017, B:B,"feb")
where H contains the sum values and A contains the year and B contains the mth

See if that will get you started?

 

[Estee28]

Thanks, I will give it a go.