Sumif Using variable names across different tabs

Chris86t

Board Regular
Joined
Feb 27, 2013
Messages
83
I have a spreadsheet which has multiple depot tabs (106 in total)

I am wanting to create a summary by depot and sum a cell range in each tab E3:E10

I have all the Depots down the left had side want to sum the cell range for each depot however Instead of having to do =SUM('Aberdeen - Depot'!E3:E10) =SUM('Cymru - East'!E3:E10)
I want to be able to just select the cell in column A and excel know what tab I want to look up against.

I tried to use Sumif Index Match and sum product however I couldn't get it to work without creating name ranges for all 106 depots

Any help would be appreciated.
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,538
Office Version
  1. 365
Platform
  1. Windows
Are the values in col A exactly the same as the name of the sheets?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,538
Office Version
  1. 365
Platform
  1. Windows
In that case you can use
=sum(indirect("'"&A2&"'!E3:E10"))
 

Watch MrExcel Video

Forum statistics

Threads
1,114,661
Messages
5,549,280
Members
410,907
Latest member
Gan_77
Top