summing multiple sub-tabs where each sub tabs row data is not the same

[waternut]

Hi All,

Not sure if the title describes it correctly but what I am looking to do is to create a formula that looks for a certain employee position across multiple tabs and adds the individual amounts from the subtabs together onto the total tab. I have attached a workbook, and as you can see, in the 1.1-1.5 the number of positions and their row location varies in each tab. What I was attempting to do was use something like SUMIF but I need it add all the tabs together and place it on the Total tab. I was trying to avoid adding multiple sumif statements (one for each tab) as my real data set has many more subtabs, and the sumif formula would get really big.

Any help is appreciated.


Thanks in advance.

Chris

[FDibbins]

1st, create a list of all sheet names, and give it the range name Sheet_Names

Sheet names
1.1
1.2
1.3
1.4
1.5

Then use this, copied down....
=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheet_names&"'!B5:B100"),B5,INDIRECT("'"&Sheet_names&"'!C5:C100")))

[FDibbins]

deleted forum-induced duplicate thread

[waternut]

As a point of clarification regarding the list that gets named, I was not aware that each entry in the list name must have an apostrophe before the sheet reference. For example my list should have been
'1.1
'1.2
'1.3
'1.4
'1.5

Thanks to FDibbins for the assistance!!!!

Chris

[FDibbins]

See the attached. (and you dont need " in front of the cell text (numbers), because the sheet names dont have that either)

IF you dont have too many tabs, or the sheet names will be easy to create (like your numbers), then just type then in. If you have a lot of sheet names, let me know and I wil give you a UDF to create them all

To create the named range, 1st, make up the list sheet names (I put them H, you can put them anywhere)
Highlight the list, right-click on then and select Name a Range.
Call it Sheet_Names
then copy/paste =SUMPRODUCT(SUMIF(INDIRECT("'"&Sheet_names&"'!B5:B100"),B5,INDIRECT("'"&Sheet_names&"'!C5:C100")))