SUMIFS and between two dates

[thart21]

I am trying to use SUMIFS to sum "Covered By Supply" where, among other criteria, the "Task - Last" dates are "> the end of the "RptgMonth" -6 days" and "<= the last day of the "RptgMonth".

=IFERROR(SUMIFS(Data[Covered by Supply],Data[CCD Bus Mo Yr],$N$1,Data[Div],
$M$1,Data[RptgMonth],$P$1,Data[Task - Last],">"&DATE(YEAR($P$1),MONTH($P$1)+1,-6,Data[Task - Last],"<="&DATE(YEAR($P$1),MONTH($P$1)+0),Data[WeekNo],D9),"-")

As an example, I want to sum where the Task - Last falls between 8/25/2011 and 8/31/2011. When I add the "<=" part to the formula I get a "too many arguments for this function" error. In looking at other posts, I see that I am formatting it correctly, but cannot see where I am getting the error. It is highlighting "Task - Last" in my formula but I think it's because it's expecting something else in it's place as far as the flow of the formula. (???) Any ideas?

Thanks!

Toni

 

[barry houdini]

Looks like you have a missing parenthesis to end the first DATE function (and the second DATE function needs a day), try this version

=IFERROR(SUMIFS(Data[Covered by Supply],Data[CCD Bus Mo Yr],$N$1,Data[Div],
$M$1,Data[RptgMonth],$P$1,Data[Task - Last],">"&DATE(YEAR($P$1),MONTH($P$1)+1,-6),Data[Task - Last],"<="&DATE(YEAR($P$1),MONTH($P$1)+1,0),Data[WeekNo],D9),"-")

...or in Excel 2007 you can use EOMONTH to simplify

=IFERROR(SUMIFS(Data[Covered by Supply],Data[CCD Bus Mo Yr],$N$1,Data[Div],
$M$1,Data[RptgMonth],$P$1,Data[Task - Last],">"&EOMONTH($P$1,0)-6,Data[Task - Last],"<="&EOMONTH($P$1,0),Data[WeekNo],D9),"-")

 

[thart21]

That was it! Thank you so much, works great! I'll try the EOMONTH function as well.