I currently have numerous SUMPRODUCT formulas that generate summary data
according to raw data in a separate worksheet and according to a specific
item that is selected from a validation list. This validation list is a list
of "areas", and the raw data mentioned above is associated with the specific
area in the list that is selected by the user. For example, there could be
250 rows of data associated with "Area 2", as part of a total of 4000 rows of
data that are associated with different "areas" than "Area 2". ("Areas" would
be listed in Column AB of the worksheet containing the data.) I now require
the ability to provide summary data based on broader "divisions". For
example, "Division 1" = "Area 2" + "Area 5" + "Area 8". Can Excel add two or
more items from the list in order to reflect the broader "divisional" data?
I attempted naming ranges, and was unsuccessful but am not sure if I
approached it properly. Adding a column for "Division" to the raw data after
the fact defeats the purpose of generating summary statistics quickly and
would serve to force an expansion of the SUMPRODUCT formulas, which is not
possible given that they are already at their cell capicty limits for
formulas. Could a Combobox be utilized?