Summarizing data

[macker]

I have a list of 4 names on worksheet 1 in column A. In column B, i would
like to compile data from other worksheets 2,3 and 4. I would like to
compile in the worksheet a total of all numbers listed under a person in all
the other worksheets. The same person may have multiple entries but i want
to be able to identify the name and have the totals in Sheet 1:
Sheet 1 - Master sheet
A2 Jim B2 total of sheets 2,3 and 4 for Jim
A3 John B3 total of sheets 2,3 and 4 for John
A4 Don B4 total of sheets 2,3 and 4 for Don
A5 Russ B5 total of sheets 2,3 and 4 for Russ

Sheet 2
A2 Russ B2 - 12
A3 John B3 - 1
A4 Russ B4 - 6
A5 Jim B5 - 32
A6 Don B6 - 5

Sheet 3
A2 Don B2 - 10
A3 John B3 - 16
A4 Jim B4 - 6
A5 Jim B5 - 2

Sheet 4
A2 John B2 - 2
A3 John B3 - 9
A4 Russ B4 - 16
A5 Jim B5 - 3
A6 Don B6 - 15
A7 Don B7 - 2

 

[Toppers]

See (new) reply to your original posting: change A4 to A2 etc

 

[Toppers]

Try this in B2 on Sheet1: it assumes other sheets are name Sheet2, Sheet3 etc

=SUMPRODUCT(SUMIF(INDIRECT("Sheet"&{2,3,4}&"!A:A"),A2,INDIRECT("Sheet"&{2,3,4}&"!B:B")))

OR

=SUMPRODUCT(SUMIF(INDIRECT("'"&$H$1:$H$3&"'!A1:A100"),A2,INDIRECT("'"&$H$1:$H$3&"'!B1:B100")))

where H1 to H3 have your sheet names (extend as required)

Copy down