Hi, I am trying to build a "smarter" personal finance spreadsheet which will do the following:

1. Take downloaded transaction data from various banks/credit card companies and automatically categorize them into categories and subcategories based on certain assigned rules - each bank/credit card company will have all the data on a single worksheet, with new data being added to the end of the worksheet [SOLVED]

2. Summarize each set of transaction data by category and subcategory into an identical format, since each bank and credit card company displays the transaction data slightly differently. [SOLVED using Pivot Tables on each worksheet]

3. Create a "master summary" worksheet which summarizes the summary Pivot Tables across each worksheet by category and subcategory so I can see overall spending from all the different banks and credit cards.

I am currently stuck on Step #3 - I tried using both the Consolidate function and a Pivot Table with multiple ranges but could not get the output I wanted as both seem to only work if I want to just summarize by Category only (but I would like to have the summary at the Subcategory level).

So far the only way I could make it work was to construct a fairly unwieldy series of SUMIFS but as I think I will end up with about 20 worksheets in all, I wanted to see if there was a more elegant solution that would be easier to maintain as worksheets are added and removed.

I am using Excel 2010 with no special add-ins. I've attached an example workbook which has 3 worksheets:

a) The first worksheet is my attempt at creating the "master summary" worksheet using a multiple range Pivot Table (with 2 other Pivot Tables as the ranges) with the columns on the left containing the result. On the right hand side of the first worksheet, I've put an example of the desired result which I constructed using the SUMIFS.

b) The second and third worksheets contain some example data.

I would be very grateful for any assistance or pointers in the right direction.

Pivot Table Multiple Range Test.xlsx