Sumifs Formula with dates that change

I have a sumifs formula with a date range that is working well. But I would like the formula to change based on the date. So if it's January I want it to pull January 1 thru January 31 and if it's March, March 1 thru March 31st. I have a table that has the dates in it, so is there a way that I can refer to that table and have it do the formula based on that table????

This is an income statement. I can make the table work for YTD, but when I want only the month I have to do a range. What I can't figure out is how to make the range dynamic to change with the different dates without having to manually do this.

Thanks

Use EOMONTH for the end date.

https://www.excel-easy.com/examples/...the-month.html

I can't look at the formula right now, I’m on an iPad.

For a date in cell A1, the first day is

Formula:

=DATE(YEAR(A1), MONTH(A1), 1)

. The last day would be

Formula:

=EOMONTH(A1, 0)

So they would be your start and end criteria for your SUMIFS, COUNTIFS, etc.

Try this in cell C7:

=SUMIFS(Table_Query_from_Timberline_Data_Source[Debit],Table_Query_from_Timberline_Data_Source[Accounting_Date],">=" & EOMONTH($A$3,-1)+1,Table_Query_from_Timberline_Data_Source[Accounting_Date],"<=" & EOMONTH($A$3,0))+SUMIFS(Table_Query_from_Timberline_Data_Source[Credit],Table_Query_from_Timberline_Data_Source[Accounting_Date],">=" & EOMONTH($A$3,-1)+1,Table_Query_from_Timberline_Data_Source[Accounting_Date],"<=" & EOMONTH($A$3,0))

Please note, cell A3 must be formatted as an actual date. It can be any date within the month. There are two SUMIFS formulas because apparently SUMIFS can't sum more than single column

You would add a criteria range and criteria to each SUMIFS formula. I'm not sure where account is in your workbook from post #4, but if you wanted to use DeptBase, it would look like this:

=SUMIFS(Table_Query_from_Timberline_Data_Source[Debit],Table_Query_from_Timberline_Data_Source[Accounting_Date],">=" & EOMONTH($A$3,-1)+1,Table_Query_from_Timberline_Data_Source[Accounting_Date],"<=" & EOMONTH($A$3,0),Table_Query_from_Timberline_Data_Source[DeptBase],$B7)+SUMIFS(Table_Query_from_Timberline_Data_Source[Credit],Table_Query_from_Timberline_Data_Source[Accounting_Date],">=" & EOMONTH($A$3,-1)+1,Table_Query_from_Timberline_Data_Source[Accounting_Date],"<=" & EOMONTH($A$3,0),Table_Query_from_Timberline_Data_Source[DeptBase],$B7)

Tonymm33 - please note that Ito mark a thread as solved, you need to select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

This data is structured differently than the previous workbook. I would use a different formula for this. In C3 and drag down:

=VLOOKUP($B3,$L$3:$O$5,MATCH(TEXT($C$2,"mmm") & "*",$L$2:$O$2,0),FALSE)

Does that give you the results you're looking for?

So for the sake of consistency, use this for a single month:

=SUMPRODUCT((MONTH(DATEVALUE((LEFT($M$2:$O$2,3)&"1")))=MONTH($C$2))*($L$3:$L$5=$B3)*$M$3:$O$5)

Use this for YTD:

=SUMPRODUCT((MONTH(DATEVALUE((LEFT($M$2:$O$2,3)&"1")))<=MONTH($C$2))*($L$3:$L$5=$B3)*$M$3:$O$5)

See if those get you the results you're looking for.