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[TABLE="width: 500"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Month[/TD]
[TD]Banker[/TD]
[TD]Segment[/TD]
[TD]Product Type[/TD]
[TD]Relationship name[/TD]
[TD]Product name[/TD]
[TD]Balances[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]Nov[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]67771[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]Dec[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]38881[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]Dec[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]38401[/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD]Jan[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]23011[/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD]Jan[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]17625[/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD]feb[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]45282[/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD]feb[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]34627[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.