Sum Across a Variable Range of Sheets Based on Multiple Criteria

[jpinho88]

Hi, I am trying to create a summary tab that will find and sum values from a variable number of sheets based on two criteria. These criteria are a revenue identifier (rows) and time period (columns). On all the sheets that I am summing, the columns and rows are not uniform, hence the need to locate the intersection of the criteria on each before summing. The number of sheets to be summed also varies so I was planning on using two blank sheets with the sheets to be summed in the middle. I was attempting to use a combination of sumproduct, sumifs, and indirect but am having trouble getting there. I searched for a while but could not locate an instance where this was addressed.

I am new to posting here but have lurked for a while and appreciate all the insight this site has to offer. Thanks in advance for the help.

Thanks!

[oeldere]

My advice is to make sure the format of the columns (and rows) are the same.

After that you can use a VBA code to get all data on a summery sheet.

After that a pivot table can do the rest of the job.

[jpinho88]

The rows and columns have the same identifiers I am using on the summary sheet and in the same format, it's just the location that is not the same. Additionally, the summary sheet I am creating will be part of an overall larger model that I am bringing in using Index/Match. Is there a formula that can be used on the summary sheet to accomplish this?

[MrShorty]

Is it necessary for the computation to take place in a single formula on the summary sheet? I prefer to use a helper cell (or cells) on each sheet with the conditional sum for that sheet (sumif() or sumifs() function). Then, a simple 3d sum (sum(sheet1:sheet5!A1) will sum up each of these cells on the summary sheet. I find this is a lot easier than trying to work through a volatile indirect function or VBA.

[jpinho88]

I see what you're saying but I wanted to do it all in one cell on the summary sheet for one reason. I receive the sheets to be summed from various sources and want to leave it as is. I basically pull those sheets into a master file where the summary sheet resides.