Aggregate multiple values into one cell based on multiple criteria using VBA

I have an Excel workbook with two sheets named Summary and Data. In Data I have data broken down by Category and by Months. I need to take data broken into many categories and summarize it down into a more high level report by combining categories. I attached this in case anyone would like to view it.

When this Workbook is opened I would like to use VBA to search the category titles found in Column A (beginning in Row 3) on the Sheet called Summary and find the data in the Data Worksheet that matches that category and total all of those entries together and place them in the correct space by Month. Hopefully that makes sense if not I am happy to clarify.

I am stuck on using two variables AND getting the multiple values to add together in the target cell. I know I could write the code to find the values and place them in rows under the category but this time I need them added and placed according to Month.

Thank you in advance for any help

Monthly Data Pull.xlsm

Richard,

In my handful of years in Excel I have never really used Pivot Tables so I had not thought about it, so I will look into it. I will let you know how it goes! Thanks to you Richard and JieJenn both!

Best,

J

So that is pretty slick and amazingly easy! The only problem I am coming up against is keeping the data in a particular order. The primary reason for this task is to take data that is in one format and in many different categories and condense it down into less categories and to keep it in a very particular order to cut time and mistakes as data is entered into our database. I really like the functionality of working with a pivot table but any suggestions on how to get that data into the right order? Also it is not alphabetical, it is based off of balance sheet and income statement Assets - Liabilities - Income - Expense...

Thanks in advance guys!