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

Jackson4242

New Member
Joined
Oct 8, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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:
excel_image_1.PNG


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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,935
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,148,269
Messages
5,745,785
Members
423,972
Latest member
franklins

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top