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

#### Jackson4242

##### New Member
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?

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### Jeffrey Mahoney

##### Well-known Member
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.

Replies
3
Views
459
Replies
4
Views
278
Replies
8
Views
160
Replies
4
Views
193
Replies
0
Views
855

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,492
Messages
5,832,010
Members
430,103
Latest member
BIGGAZ

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back