Complex problem on easily creating and editing subsets...

  • Thread starter Thread starter technoMyst
  • Start date Start date
T

technoMyst

Hey guys,

I've got a bit of a complex problem. I have a spreadsheet that lists
yearly sales information for all 50 states in the US. Then I have the
last 3 and 5 years of total sales for each state.

What I want to be able to do is create territories of these states and
then be able to get the total 3 and 5 year sales for all the states in
the defined territories. This can be done normally just by adding
things manually but I'm wondering if there’s a way, perhaps with a
macro, to define these territories and get the sums. The other problem
is I also need to be able to easily modify these territories and what
states are in them.

Ideally I'd like to have the states and their information on one sheet
and then a list of the territories and the states in them on another
sheet in the same workbook, that way it's easy to edit states.

Any help is greatly appreciated.

-Ryan Foley
 
Ryan,

Add a column to your totals that puts a grouping code in there. For
instance, in E put W for Western States, and then you can get the Western
total easily with SUMPRODUCT

=SUMPRODUCT((E1:E100="W")*(D1:D100))

D1:d100 will need to be chaged to where your amounts really are.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top