MultiTab Sumif

[JetreL]

OK, I have tried a few things with no luck and was hoping that someone here could help me out.

I am trying to do a multitab sumif. Here is a simpler breakdown of what I am trying to accomplish. I have a 5 page workbook,

Two Supervisor Pages
S1, S2
Three Worker Pages
D1, D2, D3

On all the tabs I have a cell that contains which supervisor the person belongs to. (On the Supervisor Page it's Cell A5 and on the Worker pages it's Cell C5) I am trying to do a check if the worker belongs to a supervisor and the add the appropriate cell withing the workbook.

Ex
Sheet S1 Cell A5 = Smith
Sheet S2 Cell A5 = Jones

Sheet D1

     C  D
5  Smith
..
10   7  8
11   6  8

Sheet D2

     C  D  
5  Jones 
..
10   9  8  
11   6  8

Sheet D3

     C  D  
5  Smith
..
10   6  7  
11   8  7

So on Smith's Workbook for C10 I want to do:
=Sumif(D1:D3!$C5,A5,D1:D3!$C10)
which should equal 13
So on Jones's Workbook for C10 I want to do:
=Sumif(D1:D3!$C5,A5,D1:D3!$C10)
which should equal 9

I have tried selecting all the Workers sheets and named them Agents and doing this but it doesn't seem to work.

=SUMPRODUCT(SUMIF(INDIRECT("'"&Agents&"'!C5",1),"A5",INDIRECT("'"&Agents&"'!C10")))

Any help anyone can give would be appreciated!!

 

[Dave Patton]

try
=SUMPRODUCT(SUMIF(INDIRECT("'"&Agents&"'!C5"),A5,INDIRECT("'"&Agents&"'!C10")))