Use SUMIF or SUMPRODUCT across multiple sheets using a single cell to match criteria

[nathanterhaar]

Hi,

I'm trying to sum across multiple sheets based on a single cell used as an identifier at the top of each particular sheet. I've attached a sample workbook. The formula would go in cells A3:L3 of tabs "Company Y Consol" and "Company Z Consol," based on the identifying "Y" or "Z" in cells A1 of each workbook. Therefore, each month for Company Y would return a value of "16," and each month for Company Z would return a value of "100."

I can create an extra tab that includes all of the tab names to be used in the formula with "'"&(TABNAMELIST)

Thank you for your help.

[dflak]

As the book is laid out you can use simple fixed formulas like Company Y Console Jan = 'Company Y Sheet A'!A3' + 'Company Y Sheet B'!A3.

Is there more to it that that? Instead of Sheets A and B can there be a varying number of sheets? Also do you want a generic Console meaning Whatever you put into Cell A1 determines which Company Sheets A, B, C ... etc. to look at.

All this assumes you have a strict naming convention that links sheets to companies.

[nathanterhaar]

Thanks for responding!

Yep it's a bit more complicated as your guesses state:

1. Yes, I want to be able to add and remove sheets fluidly.
2. Yes, unsure what generic console means but I would like to be able to create additional sheets and by changing cell A1 to either "Y" or "X" (or addt'l companies down the line) it will automatically be summed into the relevant consolidating company sheet.

I'm hoping it could be done by creating an extra tab list a list of sheet names: Company X Sheet A, Company X Sheet B, Company X Sheet C... then when a new tab is added you simply add the sheet name to the list. Then, using some sort of sumif/sumproduct or index/match function with the list as a reference it will automatically pull the sheets into the consolidating summary. That's how I envision it working but I can't seem to get the mechanics down myself.

[kvsrinivasamurthy]

Try this
In A4, then drag across

Please Login or Register  to view this content.

In A3, then drag horizontal

Please Login or Register  to view this content.