I have an unusually formatted data source and need to work out how to calculate a total of multiple rows and columns based on multiple criteria. A small sample is attached.
Basically the user needs to be able to choose a Location Id; A Day Qtr; and a Week Number (yellow cells at the top of my sample) and the total and average at the top needs to calculate the results (to make it clearer the total and average shown in this example would be based on the yellow cells in the sheet).
Ideally I need to be able to do it without adding extra columns because the data is extracted every week from another system in the format shown commencing at Row 6 so I would like the user to be able to simply paste the new report over the top.
Can anyone help me figure out how to calculate the total given it needs to sum multiple rows and columns?