Week to date, month to date, year to date using SUMIFS

Hello, please can someone help with my problem. I am trying to create a month to date and year to date formula using SUMIFS. I have managed to do this regarding week to date but struggling with month to date and year to date.

See example data attached.

A note on the data is that the weeks and months do not correspond to the actual weeks and months of the year. They are in line with my companies fiscal dates which do not match up to actual weeks in the year etc..

I reckon it’s a simple solution but my brain is no working anymore!

I have populated the figures I am trying to reach in cells D10 and D11.

Appreciate your help, thanks!

Try

Month-to-date

=SUMIFS('example data'!B:B,'example data'!A:A,">=" &EOMONTH($C$5,-1)+1,'example data'!A:A,"<=" &$C$5)

Year-to-date

=SUMIFS('example data'!B:B,'example data'!A:A,">="&DATE($C$2,1,1),'example data'!A:A,"<="&$C$5)

Thanks John, the year to date formula works but the month to date formula seems to fall short slightly. If I copy your formula I get 256,309, when the result I am looking for is 297,498...

Thanks again.

Check your data: you sum from J5: should be J8!

Yes that's the start of March in actual terms, but the start of March in 2017 for my company is on 26-Feb. The start of March will also be on a different date in 2016 or 2018 etc.. when referring to my company dates.

And how are we to know that month-to data are not a calendar month?

I'm hoping from introducing help columns in the example data sheet this could be done? I have matched up the company months and company month number of the year in columns E and F that correspond to the actual dates in column A.

Wish my company just used the normal dates, would make this a lot easier!

Right got you, see attached a list of our start dates for the month for this year. Thanks!

In C6 (Month Start date)

=INDEX('example data'!$A$2:$A$160,MATCH($C$3&Sheet1!$C$2,'example data'!$E$2:$E$160&'example data'!$G$2:$G$160,0))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

in C10

=SUMIFS('example data'!B:B,'example data'!A:A,">="&$C$6,'example data'!A:A,"<="&$C$5)

Update:

Based on the table you provided, you could replace formula in C6 with

=VLOOKUP($C$3,'start and end dates'!$E$4:$F$15,2,0)

Thank you John! I will try this out later on and let you know. Appreciate your help