YTD Sumifs based on Dates

[sureshsonti]

Can I get some help on YTD formula to do a comparison at a certain month based on Dates, Month and Year

I have my data in DATA sheet with DATES. Trying to use SUMIFS formula to compare 2018 vs 2017 values at a certain month. I have attached a spreadsheet with expected results in GREEN

Thanks for your help

 

[GraH - Guido]

would this work?

 

[sureshsonti]

would this work?

GraH,
Can you include the Month in the formula. I would like to show YTD values at a particular month. So when I change the month name in both YTD say MAR for both years, I should get values as of March for both years which should include values JAN to MAR.

Thanks

 

[bosco_yip]

1] In AB6, copied down :

=SUM(D6:INDEX(D6:Z6,MATCH(AB$4,D$4:Z$4,0)))

2] In AD6, copied down (or copied to AB6 and down instead of formula [1]) :

=SUMIFS(revenues[Revenues],revenues[Date],">="&0+("1/"&AD$3),revenues[Date],"<="&EOMONTH(AD$4&AD$3,0),revenues[Revenue Item],$B6)

Regards
Bosco

 

[GraH - Guido]

GraH,
Can you include the Month in the formula. I would like to show YTD values at a particular month. So when I change the month name in both YTD say MAR for both years, I should get values as of March for both years which should include values JAN to MAR.

Thanks

Thx for the like, Sonti.
Then it is in fact a YTM... Use Bosco's solutions, it is very elegant, as we are used to.
Using part of his wit, my formula could be tweaked like this for YTM.
=SUMPRODUCT((revenues[Revenues])*(revenues[Revenue Item]=$B6)*(revenues[Date]<=EOMONTH(AB$4&AB$3;0))*(revenues[Date]>=0+("1/"&AB$3)))

 

[sureshsonti]

1] In AB6, copied down :

=SUM(D6:INDEX(D6:Z6,MATCH(AB$4,D$4:Z$4,0)))

2] In AD6, copied down (or copied to AB6 and down instead of formula [1]) :

=SUMIFS(revenues[Revenues],revenues[Date],">="&0+("1/"&AD$3),revenues[Date],"<="&EOMONTH(AD$4&AD$3,0),revenues[Revenue Item],$B6)

Regards
Bosco

Thanks Bosco. Works perfectly as always

 

[sureshsonti]

Thx for the like, Sonti.
Then it is in fact a YTM... Use Bosco's solutions, it is very elegant, as we are used to.
Using part of his wit, my formula could be tweaked like this for YTM.
=SUMPRODUCT((revenues[Revenues])*(revenues[Revenue Item]=$B6)*(revenues[Date]<=EOMONTH(AB$4&AB$3;0))*(revenues[Date]>=0+("1/"&AB$3)))

Thanks GraH. What does this mean ("1/"&AB$3) in the formula. Thanks