Complex Process Needed

**tomwins** · 05-30-2013, 02:13 PM

I have a list of monthly costs that need to be summed according to several varying properties each month.
Please see the attached file for a sample. It is just a sample; my real situation has hundreds of items to sum and then sum according to eight or more different properties.

Several people have costs which I need to sum by a quality of the people. For my example, I am saying the qualities are 1) city, 2) Hair Color, and 3) Owner (Yes/No). So each person's cost will be summed several times.

In the past, I've concatenated the name and cost and then used SUMIF and VLOOKUP formulas to get the information I need. The problem with this is that anytime something changes (say someone changes from Owner-No to Owner-Yes) I need to go in and change the SUMIF formula.

This is complex for me because the categories change (maybe not month to month) but throughout the year and, as I mentioned before, there are hundreds of these.

The outcome I need is a process to automate getting the following results:
Costs by City:

San Francisco 480
LA 430
San Diego 400

Costs by Owner

Yes 755
No 555

Costs by Hair Color

Brown 735
Gray 155
Red 290
Blond 130

In addition, I will need to sum the Salary costs, Inventory costs, and other costs.

I'm wondering if using Named Ranges and/or macros are a better way to go? That's where I am stuck. Any suggestions on making this easier is very much appreciated.