Dynaimc Sum Range

Hi Excel Savants!

Im getting tired of manually updating my sum ranges in my Workbook. Heres the situation.

We r working with a 3 month average. So for example in august we need to take july june & may and divided by the numerator. The problem im facing is that i need to work back from august. All the formulas are on summary page referencing to the data worksheet.

Any thoughs on how to structure this so that each month i found possiably reference the current month on the summary page and have the sum function move and sum the prior 3 months? Maybe use index match ???

Any thoughts would be great, thanks!

Equation: sum(d6:f6) now we have the sum of april - june assuming the woorksheet month is july.

Next month august, the equation will need to be update for reflect may - july (e6:g6)

On top of the nunbers we r summing on the data sheet are the months. Same format as the current month on the summary page

Im thinking i could use edate and index but not exactly sure how, thoughts?!

Here is a sample of what I'm trying to do! Please help! Thank you!

I see what you are doing, but my hope is that I can leverage the data validation list with the months so that I can change that month in the list and the formula will automatically update accordingly... I'm thinking using edate and index could work but not sure how to do it.... Thoughts?

That's an awesome formula and I will use that in the future. Thank you for your time; not many people would help out like that.

For this particular instance, I will have to use prior year information as well so I think the Month equation may not work..... I've updated the sample. For example for Feb 2012, I would need to use Jan 2012 Dec 2011 and Nov 2011 data. I feel like an edate formula would work has anyone used that before?

What does that mean? ''''^''''1

=SUM(OFFSET(Data!A2,,MATCH(B1,Data!A1:L1,)-4,,3))/SUM(OFFSET(Season!A2,,MATCH(B1,Season!A1:L1,)-4,,3))

Teethless that works AWESOME! Can you explain it? I'm confused when you do *Data!A2:L2 ? Thank you soooo much!!!

Do you think it's posiable to reference the date on the summary page for a full year sum? So it it's august he sum would be jan - aug. when the date is changed to sep the formula would sum jan-sep?

Originally Posted by Excel"*"

Do you think it's posiable to reference the date on the summary page for a full year sum? So it it's august he sum would be jan - aug. when the date is changed to sep the formula would sum jan-sep?

Yes it's possible. See the attached

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.

Thank you so very much! Sorry for the confusion; your time is greatly appreciated. Very smart Teethless mama!!! Do you even have to think about this! haha

You're Welcome!

The formula in the attached file was working yesterday but now it's now!!! Any thoughts?! The formula was...

I tried to do Ctrl+Shift+Enter for the array but it's not working....... I turned on my analysis toolpack for edate as wel..

=SUMPRODUCT((EDATE(B1,{-1;-2;-3})=Data!A1:H1)*Data!A2:H2)/SUMPRODUCT((EDATE(B1,{-1;-2;-3})=Season!A1:L1)*Season!A2:L2)

It was working on my Mac if that helps @ all.

Yes the formula does what I need it to due assuming the words for my issue is correct.. Any thoughts on how to get it working in 2003?

Thanks that does work really well, do you know how you would add an indirect so I could change that data worksheet to whatever sheet I had in a data validation list? I tried to add it but I think my syntax is off... Like most time! Thanks all!

Im struggling with the correct syntax, i keep getting an error...... Thoughts....

This is my shot, I replaced like you said but I get a Ref.... Thanks for your help, thought this would be easy. Sure you will make it look that way!

I got it to work using this:

=SUMPRODUCT((INDIRECT(E1&"!A1:L1")<B1)*(INDIRECT(E1&"!A1:L1")>=EDATE(B1,-3)),INDIRECT(E1&"!A2:L2"))

Thank you for your time, you really know you Excel!! Thank you again.

Can u explain what you did after the * sign... It does not seem like an array....

*(INDIRECT(E1&"!A1:L1")>=EDATE(B1,-3)) that section I don't understand. I was under the impression that you used * when the array is not the same... Can you explain this... Why did you use >=?