can't wrap my mind around this one. We have data in a sheet with columns with a date header (Jun 2017,Jul 2017,Aug 2017) and these are 'rolling headers' (they give information from the current date - Now in March the first month shown is April 2017 - In April the first header will be May 2017. I need to be able to sum the cost in those column based on a certain period, like giving the total for a full year two years from a given date. I tried the below, but it gives me a #Value error
=SUMIFS(Calc1!EZ2:HR10000,Calc1!EZ1:HR1,">"&DATE((YEAR(Calc1!EX2)+2),1,1),Calc1!EZ1:HR1,"<"&DATE((YEAR(Calc1!EX2)+3),1,1))
Also marking it as an array formula gives me the same result.
What am I doing wrong and is there a better way (use aggregate?).
Thanks, Rene