Sumproduct formula failing after turn of the year

This sumproduct formula has failed since the turn of the new year.
I use it on both open and closed workbooks that span from one year to the next, ie 2016 / 2017.

Could you please help me to get it working across the year boundry.

=SUMPRODUCT(('Orders'!$E$13:$E$309)*('Orders'!$D$13:$D$309<=DATEVALUE(TEXT(TODAY(),"dd-mm-")&2016))*('Orders'!$H$13:$H$309='Orders'!$D$2))

Thanks in anticipation.

You have hard-coded 2016 in the formula, so you are looking for dates that are less than 01/01/2016. I think it would be better to have this as your middle term:

... *('Orders'!$D$13:$D$309<=TODAY())* ....

Hope this helps.

Pete

Hi Pete,
Thanks for your reply, unfortunately by using your fix the formula returns the total for the period rather than the amount up to and including todays day & month.

The sheets could be 2016/17, 2015/16, 2014/15 etc

In your formula I only see references to an Orders sheet, so I'm not sure what your final sentence means.

You said that the formula stopped working at the turn of the year (i.e. today), and I can see that you want to sum column E when column D is less than or equal to some date and column H is equal D2. The date is given by today's day and month, with the year set to 2016, so if you were using this formula (successfully) on 1st December last year, say, then the date you are comparing with would be 1st December 2016 (i.e. the same as the TODAY() function would return on that day), which is why I suggested that change.

I think you need to explain more fully what you want to achieve, and may attach a sample Excel file.

Hope this helps.

Pete

Ok, solved the issue, all working perfectly now.

I replaced DATEVALUE(TEXT(TODAY(),"dd-mm-")&2016))

with DATEVALUE(TEXT(TODAY(),"dd-mm-")&RIGHT($B21,2))

Where b21 contains the year data, such as 2016/17

Thanks for trying to help, I'll close this post now.

Glad you were able to solve it - thanks for feeding back (I don't think anyone could have guessed that you had 2016/17 in cell B2 !!).

Pete