multiple spreedsheets need help summing based on a text entry

[Drasnia]

I have an interesting problem, really hoping ya'll can solve this bit.
I have an excel spreedsheet that has multiple sheets with columns that
need to be summed on a summary sheet. For instance

Sheet1
NAME VALUE

Tony 1
Jenny 2
Todd 3
Roger 4
Sally 5

Sheet2
Carie 1
Tony 1
Jenny 4
Roger 4
Roger 5

Sheet3
Tony 1
Tony 3
Sally 2

How do I take everyone that is listed on the 3 sheets and have excel
calculate that Tony has a TOTAL value from the 3 sheets of 6 on a
summary sheet? As you can see it gets tricky as Tony can be listed
multiple times or not at all on the different sheets.

ANY advice would be appreciated. thanks

 

[Frank Kabel]

Hi
one way:
- enter your worksheetnames which could contain the values into a
separate range and define a name (goto 'Insert - Name - Define') for
this list of worksheet names. Lets assume you call this list 'wslist'
- now enter the following formula

=SUMPRODUCT(SUMIF(INDIRECT("'" & wslist
"'!A1:A100"),"Tony",INDIRECT("'" & wslist & "'!B1:B100")))