Help using Names Ranges to sum data in rows across variable numbers of columns

Hello all - first post here, so hope it is a good one! I was reading the forum today and got a TON of great help, so wanted to thank everyone in advance because it was very useful. As I was reading though, I learned a few things, but then stumbled upon a couple things that stumped me...

Some background - I am well versed in Excel (currently using 2007) and have been using it for several years. However, I was never a progammer, and I learned everything on my own, so my technique could probably be described as "it works, but could be more efficient." Well, I am hopefully going to become a little more efficient now with your help....so on to my challenge!

I have attached a dummy workbook...two sheets, data and then a summary tab. I basically have sales data arranged by geography (in rows), and by time (months - in columns). Pretty self explanatory.

What I want to do is to have an efficient way to create aggregations of this data in my summary tab. I put together a simplified version in that tab, both with the approach I typically take (in red), plus two other methods I've been trying to figure out today using named ranges (green and blue examples).

I put notes next to each example and you can see my formulas...so my two questions are:

1) Generally speaking, for these types of aggregations, what is the most efficient way to tackle it? named ranges? using SUMIF, SUMPRODUCT, INDEX, or any array-based formulas?? (I need help with arrays btw)

2) After we figure out #1, because I will be using year-to-date aggregations, is there a way that I can dynamically have ranges updated each month with new data? Fore this, assume that new columns will NOT be appended, but I will simply lose a month of history...ie, if I have 24 months of data, then next month I will have 24 also, but one new month and lose one old month

Open to ideas and best practices!!! Thanks a ton and glad to be here!

I would probably do it like this:

In summary!A2: have a data validation drop down of the months, linked to your Sales sheet row 1
In summary!B2: have a data validation drop down of the months, again linked to your sales sheet row 1
These would become your start and end dates. You can then use this formula in B3:

In A3:A8 have your geography headings, then in b3:
then in b3:
=SUM(INDIRECT(("Sales!"&ADDRESS(MATCH($A3,Sales!A$1:A$7,0),MATCH(Summary!A$2,Sales!A$1:V$1,0))&":"&ADDRESS(MATCH($A3,Sales!A$1:A$7,0),MATCH(Summary!B$2,Sales!$A$1:$V$1,0)))))
and copy down.

Thank you very much for taking the time, Yudlugar. I really appreciate it. I will definitely use your formula in the future, but i have some concerns with that approach for this exercise that I'm working on for a couple reasons....

1) It is meant for an audience that isn't too Excel/analytically savvy...they are used to the aggregates I had listed (rolling 3, 6, year to date, etc), so for user friendliness, I would prefer the simpler approach

2) THere is a formulaic reason I think I need to stick with the groups mentioned above. In subsequent columns in the Summary tab, I will be doing calculations based on the numbers in the selected time period. This presents a challenge because of the following:

a) Calculating growth rate vs prior period - In our workplace conventions, this means different things based on the time period selected. For example, there are Rolling Monthly measures and Year To Date measures. Ex/ Rolling 3 growth % is the current 3 months vs the prior 3 months (ie, Jul - Sep vs Apr - Jun)...so this is consecutive. However, if I use a Year to Date, it is vs the same time last year - ex/ Jan13 - Sep13 vs Jan12 - Sep12. I don't see any way to efficiently incorporate this with the formula shown.

I think Naming Ranges are the way to go so I can have my aggregates defined, so my question is, what's the best way to do this?

1) I could name ranges for each geography and each time period, but this gets cumbersome. If I have more geographies, more time periods, and more products (see in the worksheet, I included Product 2 in the Sales tab - it is in additional columns "wide and fat"

2) Can I simply name ranges inclusive of all geographies (ie, multiple columns and rows) and use a formula to return the respective geographies in the summary tab? I think this may be what an array is, but I am not well versed on them at all.

So, long story short, if Option 1 is best, can I copy/paste down formulas in my summary tab and have the Named Ranges change as the reference row (geography) changes? In my worksheet, I have noted this. If Option 2 is best, I will read up on Arrays, but any formulas are much appreciated!

Take a look at my workbook with some updated info to follow along what I'm saying. Thanks again!

Update - I figured out a way to do it with Named Ranges and Array formulas...

I went with Option 2 above and renamed my ranges to have them just time-based (inclusive of all geographies). I then used various nested IFs can calculations all based this general format: {=SUM((Named Range)*(Geography=A1)}

Question since I'm new still...since I answered my own question, do I edit this thread as solved? I'd still be curious to hear other thoughts with other approaches etc, but I want to be a good citizen around here.