Vlookup and Sum formula help for data across several worksheets

[SJDmidlands]

Hi everyone, I wondered if someone could help me with a formula to sum quantities that meet certain conditions? I've played around with VLookup, SumIf, SumProduct, but don't seem to be able to get it right. I have several worksheets in a workbook, one for each month of the year, and then a master worksheet to record totals of each item (total sales per location, total purchases per location). I've uploaded a very basic version of my spreadsheet, which shows the type of thing I'm looking to achieve.

From the attached spreadsheet, I need to create formulas in the master worksheet's yellow-highlighted cells to give me total quantities of North sales per item, total North purchases per item, total South sales per item, and total South purchases per item. This is a very basic example of my real spreadsheet, which contains around 500 items and around 5,000 transactions per month, so I'm thinking that a combination of VLookup and Sum functions are what I need to achieve, but I may be wrong and would welcome any alternative suggestions to achieve this.

Any help with this would be really appreciated.

[Roel Jongman]

you have made the classic mistake breaking you basedate in to pieces, in this case of 1 month and then try to get a total, this is very hard to do. It is like to reconstruct a pie from 12 pieces back to 1 piece..
Everytime a sheet with date is added you need to redo your formulas.. It will be a high maintenance sheet.

You should all data in 1 master sheet and then take the slices of data you need for the report.
I have used a pivottable to summary the data into months. This way you can show the totals broken down

[SJDmidlands]

Hi Roel, thank you so much for guiding me in the right direction! We run monthly reports so had it in mind to just add these as new tabs each month, not thinking that it would be easier to put the data together. I've put this into action (having to clean up the data a bit, but that's a one-off), and using the GETPIVOTDATA function to pull the data through from the pivottable to my master sheet works a treat. Next step for me is seeing how it handles new data each month (so that I can add a "last 12 weeks issued' column). I'm hopeful that this won't be an issue now that the framework is in place (I'm thinking: 1. add the data to the base data, 2. refresh the pivottable, 3. have my "last 12 weeks issued" column set up on the master sheet with a relevant formula - not quite sure how to get it to return the sum of the last 12 weeks, but sure that its possible.

A massive thanks Roel, you're a star!