Excel 2000

I use numerous SUMPRODUCT formulas that generate results based on an item
selected from a validation list. These items are "areas" within a
"division". The worksheet from which the formulas draw the dats is organized
by "area". What I need to accomplish is to provide results based on a
"division", which is made up of 2 or more "areas". My preference is to find
a solution that recognizes each "area" and subsequently, that a specific
"division" = "area 1" + "area 3" + "area 6", for example. Modifying the
formulas will prove very onerous and may prove impossible, due to the length
of many of the formulas. Changing the formulas will also likely mean
changing the data from which the formulas draw, which defeats the purpose.
Can named ranges be used? If so, how can I use the named ranges to achieve
this result? I have attempted the named range approach, but have been
unsuccessful - I'm likely doing something wrong if that's where the solution
lies.

Thanks!