dynamic formula using date range of TODAY and Today+90

[marino3620]

I am looking to SUMIFS the costs in Column I when the values in Column H are ,<=TODAY() and >=TODAY()+90, and value in column M is "DTB" (with DTB to be able to change based on the individual members of team).

I also do not want to have to keep going back and changing cell range to include all cells when new data is added

Here is what I have:

=SUMIFS(I5:I468,H5:H468,"<="&TODAY(),H:H,">="&TODAY()+90),$M$5:$M$468,"=DTB")

thank for your help.

I will then need to take same formula and change the criteria to SUMIFS by month instead of [&TODAY(),H:H,">="&TODAY()+90)]

 

[Jonmo1]

You have the < and > criterias reversed.
It's not possible for any date in H to be Less than Today AND Greater Than Today+90

And you have H:H in one criteria, but H5:H468 in the other...
All ranges need to be the same size

Try
=SUMIFS(I:I,H:H,">="&TODAY(),H:H,"<="&TODAY()+90),M:M,"=DTB")

 

[marino3620]

Thanks for the help. I am still getting an error on this. When I expand the function and look at the arguments, both TODAY functions say "Volatile". I can send a sample spreadsheet if that helps.

 

[Jonmo1]

Ah, there was an extra ) after the +90
=SUMIFS(I5:I468,H5:H468,"<="&TODAY(),H:H,">="&TODAY()+90),$M$5:$M$468,"=DTB")

Try
=SUMIFS(I5:I468,H5:H468,"<="&TODAY(),H:H,">="&TODAY()+90,$M$5:$M$468,"=DTB")

 

[marino3620]

Thanks! this worked: =SUMIFS(I5:I468,H5:H468,"<="&TODAY(),H:H,">="&TODAY()+90,M:M,"DTB")
how can I change the criteria to be a single Month instead of the "today to today+90" range? My dates are written as 2/2/2014 etc. DO I need a simple formula to pull out just the month into its own column then use that column as my criteria range?

 

[Jonmo1]

You can do this for November 2014

=SUMIFS(I5:I468,H5:H468,"<="&DATE(2014,11,30),H:H,">="&DATE(2014,11,1),M:M,"DTB")

 

[marino3620]

That is AWESOME!!!! You have really taught me a ton this morning!!! I am so very grateful!

Can you set it up to have 1 formula that will return 12 different values (one for each month)?

 

[Jonmo1]

If you put your month numbers (1 through 12) in a range, say A1:A12

Then this in one cell will do Januray, fill down 11 more cells for Feb Mar etc..

=SUMIFS(I5:I468,H5:H468,"<="&DATE(2014,A1+1,0),H:H,">="&DATE(2014,A1,1),M:M,"DTB")