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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,287
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,287
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,314
Messages
5,547,170
Members
410,775
Latest member
alal1030
Top