Dynaimc Sum Range

How would you incorporate the edate idea you had? so I can reference the month and it will go one back and sum from jan. So if it says Aug it will sum from Jan-July? Wow you really know your Excel!! Below is my try... Thanks for the learning opportunity!

Originally Posted by Excel"*"

How would you incorporate the edate idea you had? so I can reference the month and it will go one back and sum from jan. So if it says Aug it will sum from Jan-July? Wow you really know your Excel!! Below is my try... Thanks for the learning opportunity!

B1: Your current month

=SUMPRODUCT((EDATE(B1,{-1;-2;-3})=Data!F1:Q1)*Data!F2:Q2)

This formula will adjust automaticly

It seems that the edate part is taking the last 3 months, I'm looking for an equation that would look @ B2 (the date) minus one month then sum to Jan from that date. It seems like I need to start with Jan than use edate -1.. with match index but not really sure how to do that?

Does what I'm thinking make sense? Thanks for your time!

Very confusing. In your speadsheet you said "I would like the above equation to use the Date in B1 to automatically go back 3 months from that date. So in September, the equation in B3 would take the sum of Aug July & June. Note) I can not re-structure the data. Now, you said "I'm looking for an equation that would look @ B2 (the date) minus one month then sum to Jan from that date. It seems like I need to start with Jan than use edate -1.. with match index but not really sure how to do that?

B1: your current date

=SUM(Data!F2:INDEX(Data!F2:Q2,MATCH(Summary!B1,Data!F1:Q1,0)-1))

This formula is sum up from January to your curent month -1 month.