Adding cells in Column B on multiple sheets where Column A matches

[Ahijado]

Sheet 1 called January


[TABLE="width: 323"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]Sum of Hours[/TD]
[TD]Sum of Closed Tix[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD="align: right"]156.5[/TD]
[TD="align: right"]68[/TD]
[/TR]
[TR]
[TD]Doe, Jane[/TD]
[TD="align: right"]176[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD]Sizzors, Edward[/TD]
[TD="align: right"]11.5[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]White, Bryce[/TD]
[TD="align: right"]144[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD]Bravo, Brenda[/TD]
[TD="align: right"]158.5[/TD]
[TD="align: right"]56[/TD]
[/TR]
[TR]
[TD]Deed, Richard[/TD]
[TD="align: right"]183.25[/TD]
[TD="align: right"]105[/TD]
[/TR]
[TR]
[TD]Thelusma, Jessica[/TD]
[TD="align: right"]160.25[/TD]
[TD="align: right"]37[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]990[/TD]
[TD="align: right"]338[/TD]
[/TR]
</tbody>[/TABLE]




Sheet 2 called February
[TABLE="width: 323"]
<colgroup><col><col><col></colgroup><tbody></tbody>[/TABLE]



[TABLE="width: 328"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]Sum of Hours[/TD]
[TD]Sum of Closed Tix[/TD]
[/TR]
[TR]
[TD]Deed, Richard[/TD]
[TD="align: right"]83.75[/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD]Bravo, Brenda[/TD]
[TD="align: right"]122.5[/TD]
[TD="align: right"]41[/TD]
[/TR]
[TR]
[TD]White, Bryce[/TD]
[TD="align: right"]155.25[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD]Sizzors, Edward[/TD]
[TD="align: right"]166.5[/TD]
[TD="align: right"]82[/TD]
[/TR]
[TR]
[TD]Doe, Jane[/TD]
[TD="align: right"]143[/TD]
[TD="align: right"]63[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD="align: right"]136.5[/TD]
[TD="align: right"]39[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]807.5[/TD]
[TD="align: right"]291[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 3 called Year



[TABLE="width: 323"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]Sum of Hours[/TD]
[TD]Sum of Closed Tix[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Doe, Jane[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Sizzors, Edward[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]White, Bryce[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Bravo, Brenda[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Deed, Richard[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Thelusma, Jessica[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0

[/TD]
[/TR]
</tbody>[/TABLE]



So here is the question......

Can someone please tell me the equation to put into the "Sum of Hours" column on the 3rd sheet called Year that will add up the cells in the "Sum of Hours" columns on sheets January and February where column A's names match.

An example is that Smith, John's sum of hours on the Year sheet should equal 293 because on the first sheet his "Sum of Hours" column has 156.5 and on the second sheet it has 136.5.

The main key to this is that the order of the names may not match on sheets. They will always be on Column A but will not always match.

 

[JoeMo]

Assuming you are using Excel 2007 or later version, and first name is in A2 of Year sheet, then in B2 of Year sheet and copy across and down:

=IFERROR(VLOOKUP($A2,January!$A2:$C100,COLUMN(B:B),0),0)+IFERROR(VLOOKUP($A2,February!$A2:$C100,COLUMN(B:B),0),0)

Adjust lookup ranges to cover your data.

 

[texasalynn]

try this

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Sum of Hours	Sum of Closed Tix									Sheets
2	Smith, John	156.5	68									January
3	Doe, Jane	176	35									February
4	Sizzors, Edward	11.5	4
5	White, Bryce	144	33
6	Bravo, Brenda	158.5	56
7	Deed, Richard	183.25	105
8	Thelusma, Jessica	160.25	37
9		0	0
10		0	0
11		0	0
12		0	0
13	Grand Total	990	338
Year

Cell Formulas
Range	Formula
B2	=SUMPRODUCT(SUMIF(INDIRECT("'"&months&"'!A"&ROW(A2)),$A2,INDIRECT("'"&months&"'!B"&ROW(B2))))
B13	=SUM(B2:B12)
C2	=SUMPRODUCT(SUMIF(INDIRECT("'"&months&"'!A"&ROW(B2)),$A2,INDIRECT("'"&months&"'!C"&ROW(C2))))
C13	=SUM(C2:C12)

Named Ranges
Name	Refers To	Cells
months	=Year!$L$2:$L$3

sorry had to modify formula

 

[texasalynn]

oops still missed it - test it now these work

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Sum of Hours	Sum of Closed Tix									Sheets
2	Smith, John	293	107									January
3	Doe, Jane	319	98									February
4	Sizzors, Edward	178	86
5	White, Bryce	299.25	57
6	Bravo, Brenda	281	97
7	Deed, Richard	267	147
8	Thelusma, Jessica	160.25	37
9		0	0
10		0	0
11		0	0
12		0	0
13	Grand Total	1797.5	629
Year

Cell Formulas
Range	Formula
B2	=SUMPRODUCT(SUMIF(INDIRECT("'"&months&"'!$A$2:$A$10"),A2,INDIRECT("'"&months&"'!$B$2:$B$10")))
B13	=SUM(B2:B12)
C2	=SUMPRODUCT(SUMIF(INDIRECT("'"&months&"'!$A$2:$A$10"),A2,INDIRECT("'"&months&"'!$C$2:$C$10")))
C13	=SUM(C2:C12)

Named Ranges
Name	Refers To	Cells
months	=Year!$L$2:$L$3

 

[Ahijado]

oops still missed it - test it now these work
excel 2010

	a	b	c	d	e	f	g	h	i	j	k	l
name	sum of hours	sum of closed tix	sheets
smith, john		january
doe, jane		february
sizzors, edward
white, bryce
bravo, brenda
deed, richard
thelusma, jessica
grand total

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[td="align: Center"]1[/td]

[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]

[td="align: Center"]2[/td]

[td="align: Right"]293[/td]
[td="align: Right"]107[/td]

[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]

[td="align: Center"]3[/td]

[td="align: Right"]319[/td]
[td="align: Right"]98[/td]

[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]

[td="align: Center"]4[/td]

[td="align: Right"]178[/td]
[td="align: Right"]86[/td]

[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]

[td="align: Center"]5[/td]

[td="align: Right"]299.25[/td]
[td="align: Right"]57[/td]

[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]

[td="align: Center"]6[/td]

[td="align: Right"]281[/td]
[td="align: Right"]97[/td]

[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]

[td="align: Center"]7[/td]

[td="align: Right"]267[/td]
[td="align: Right"]147[/td]

[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]

[td="align: Center"]8[/td]

[td="align: Right"]160.25[/td]
[td="align: Right"]37[/td]

[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]

[td="align: Center"]9[/td]
[td="align: Right"][/td]
[td="align: Right"]0[/td]
[td="align: Right"]0[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]

[td="align: Center"]10[/td]
[td="align: Right"][/td]
[td="align: Right"]0[/td]
[td="align: Right"]0[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]

[td="align: Center"]11[/td]
[td="align: Right"][/td]
[td="align: Right"]0[/td]
[td="align: Right"]0[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]

[td="align: Center"]12[/td]
[td="align: Right"][/td]
[td="align: Right"]0[/td]
[td="align: Right"]0[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]

[td="align: Center"]13[/td]

[td="align: Right"]1797.5[/td]
[td="align: Right"]629[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]

</tbody>

year​

[table="width: 85%"]
<tbody>[tr]
[td]worksheet formulas[table="width: 100%"]
<thead>[tr="bgcolor: #dae7f5"]
[th="width: 10"]cell[/th]
[th="align: Left"]formula[/th]
[/tr]
</thead><tbody>[tr]
[th="width: 10, bgcolor: #dae7f5"]b2[/th]
[td="align: Left"]=sumproduct(sumif(indirect("'"&months&"'!$a$2:$a$10"),a2,indirect("'"&months&"'!$b$2:$b$10")))[/td]
[/tr]
[tr]
[th="width: 10, bgcolor: #dae7f5"]c2[/th]
[td="align: Left"]=sumproduct(sumif(indirect("'"&months&"'!$a$2:$a$10"),a2,indirect("'"&months&"'!$c$2:$c$10")))[/td]
[/tr]
[tr]
[th="width: 10, bgcolor: #dae7f5"]b13[/th]
[td="align: Left"]=sum(b2:b12)[/td]
[/tr]
[tr]
[th="width: 10, bgcolor: #dae7f5"]c13[/th]
[td="align: Left"]=sum(c2:c12)[/td]
[/tr]
</tbody>[/table]
[/td]
[/tr]
</tbody>[/table]

[table="width: 85%"]
<tbody>[tr]
[td]workbook defined names[table="width: 100%"]
<thead>[tr="bgcolor: #dae7f5"]
[th="width: 10"]name[/th]
[th="align: Left"]refers to[/th]
[/tr]
</thead><tbody>[tr]
[th="width: 10, bgcolor: #dae7f5"]months[/th]
[td="align: Left"]=year!$l$2:$l$3[/td]
[/tr]
</tbody>[/table]
[/td]
[/tr]
</tbody>[/table]

you rock!!!!