Index Match Summary from different worksheets

[Markalon]

I have a workbook with a Summary page and 5 employee pages:

Tabs are: Summary, Name1, Name2...Name5

On the summary page I have columns: Job (which is our job number), Panel (number of hours), Wire (number of hours) ... and others, all numbers of hours.

On each of the employee pages I have columns: Job (which is our job number), Panel (number of hours), Wire (number of hours) ... and others, all numbers of hours.

What I'm trying to do is for a given job, sum all the hours for each operation (Panel, wire, etc) and put the total in the summary page.

So on the summary page, lets say I have the following data:

Summary Page
[TABLE="width: 500"]
<tbody>[TR]
[TD]Job[/TD]
[TD]Panel[/TD]
[TD]Wire[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


On two employee pages I have:


Name1 Page
[TABLE="width: 500"]
<tbody>[TR]
[TD]Job[/TD]
[TD]Panel[/TD]
[TD]Wire[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]5[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]


Name2 Page
[TABLE="width: 500"]
<tbody>[TR]
[TD]Job[/TD]
[TD]Panel[/TD]
[TD]Wire[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]6[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]


I want the summary page to show:
Summary Page
[TABLE="width: 500"]
<tbody>[TR]
[TD]Job[/TD]
[TD]Panel[/TD]
[TD]Wire[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]9[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]6[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]


Can somebody help me to figure out how to do this? Any and all help is appreciated. Thank you.

 

[Brombrough]

Hi Markalon,

On your summary sheet paste the following formulas.

B2 =SUMIFS(Name1!$B:$B,Name1!$A:$A,$A2)+SUMIFS(Name2!$B:$B,Name2!$A:$A,$A2)+SUMIFS(Name3!$B:$B,Name3!$A:$A,$A2)+SUMIFS(Name4!$B:$B,Name4!$A:$A,$A2)+SUMIFS(Name5!$B:$B,Name5!$A:$A,$A2)

C2 =SUMIFS(Name1!$C:$C,Name1!$A:$A,$A2)+SUMIFS(Name2!$C:$C,Name2!$A:$A,$A2)+SUMIFS(Name3!$C:$C,Name3!$A:$A,$A2)+SUMIFS(Name4!$C:$C,Name4!$C:$C,$A2)+SUMIFS(Name5!$C:$C,Name5!$C:$C,$A2)

When Row 2 fields show correct values just fill down the formula's

P.s Make sure you add all your Job numbers to your summary page.

Hopes this works for you and enjoy.

Don't forget to post Thanks/Live if it works for you.