Use array formula with index and indirect to find and add up values in multiple sheets

[Jackson4242]

I have an excel file where each sheet contains data for a different city (eg. New York City, San Francisco, Miami) and has a sheet name that matches that city. Each sheet contains a table with various monthly financial information:

In a new sheet, I'm trying to find an elegant and scalable way to replicate the table above, where each entry represents the sum of the values from the other sheets (i.e. each value represents the sum of the values from the other cities). I'd like to:

• Use INDIRECT and some sort of array to reference a list of sheet names (cities), rather than adding multiple INDEX(MATCH(MATCH()) functions together. That way if I add a new city in a new sheet I can just add it to the city list.
• Search on two row criteria (column A = "Forecast")*(column B = [Gross Revenue, Cost of Sales, etc.]) and one column criteria (row 4 = [month]).
• (Ideally) account for the fact that the financial information table in each sheet may not be in exactly the same spot (for example, the months may be listed in row 6 instead of row 4).

My current plan involves copying and pasting the financial table from each city into a new sheet, then using SUMIFS on that sheet, which feels way too messy. Does anyone have a better idea of how to do this?

 

[Jeffrey Mahoney]

Well, if you can't put the rows of financial data on the same lines for each sheet, then maybe create a summary table for each city sheet on the summary sheet.

Sumifs doesn't work when trying to reference multiple sheets like this: =SUMIFS(Miami:Seattle!D2:D30,Miami:Seattle!B2:B30,Sheet2!B12)

So create summary tables using Sumifs for each single city sheet and then Sum those to the left. The formulas in the summary tables can use INDIRECT so you can put the name of the sheet at the top of each table, copy it for a new city and change the name real quick.
=SUMIFS(INDIRECT(D10&"!D2:D30"),INDIRECT(D10&"!B2:B30"),Sheet2!B12)
Where cell D10 is the name of the city and B12 is the type of cost from column B

Of course your formulas to the left summing all the city data will have to account for the new city tables either by editing them as you go or planning ahead of time by putting in dummy tables to add data later.