multiple spreedsheets need help summing based on a text entry

  • Thread starter Thread starter Drasnia
  • Start date Start date
D

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
 
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")))
 
Back
Top