matching and summing across sheets

[Todd Lietha]

Can someone help me out with a formula to produce the results in Column I in
the following example?

Sheet Name: Master
A B C D E F G H I

111 AAA Todd 2 Tim 4 1
222 ABB Tony 6 Todd 5 3
333 BBB Tim 8 Sue 3 Jim 7 11

Sheet Name: Todd
111 50 1
222 100 0

Sheet Name: Tim
111 100 0
333 50 4

Sheet Name: Tony
222 50 3

Sheet Name: Sue
333 100 0

Sheet Name: Jim
333 0 7

Basically, for a given row in the Master sheet, for each name in C, E, and
G, go to the sheet with the same name, locate the row where A matches, find
the value in C, and sum the values.

Thanks in advance!

 

[Domenic]

Try...

I1, copied down:

=SUMPRODUCT(SUMIF(INDIRECT("'"&{"Todd","Tim","Tony","Sue","Jim"}&"'!A1:A1
00"),A1,INDIRECT("'"&{"Todd","Tim","Tony","Sue","Jim"}&"'!C1:C100")))

or

=SUMPRODUCT(SUMIF(INDIRECT("'"&$M$1:$M$5&"'!A1:A100"),A1,INDIRECT("'"&$M$
1:$M$5&"'!C1:C100")))

....where M1:M5 contains the sheet names, Todd, Tim, Tony, etc.

Hope this helps!

 

[Todd Lietha]

Thank you! I used the INDIRECT version and it works wonderfully for me.

 

[Todd Lietha]

I used your second suggestion in my prototype and it worked well as I had a
known fixed length list of names. But in production, the list of names
(Todd, Tim, Tony, etc...) is in a separate sheet named Resources and I do not
necessarily know the length of the list of names.

The list starts in cell C5 and runs vertically to cell 45 (C5:C45). This
week there might be 8 names on the list and next week there might be 10 or 12
names.

If I use a range of C5:C45, I get a #REF error. But if I limit the range to
only the populated cells (e.g. C5:C12 for eight names), it works fine.

Do to the number of instances of the spreadhseet, it isn't practical to edit
the formula each time the size of the list changes.

Any ideas on how to overcome this?

Thank you in advance.

 

[RagDyer]

First of all, another referencing approach with this type of formula is to
use a named range, where the cell range containing the sheet names is
assigned it's own name.

So, if you selected C5 to C12 in the Resources Sheet, clicked in the name
box (left of the formula bar), and typed in:
List
Then <Enter>,
You could revise your formula *FROM*:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Resources!C5:C12&"'!A1:A100"),A1,INDIRECT("'"&Resources!C5:C12&"'!C1:C100")))

*TO*

=SUMPRODUCT(SUMIF(INDIRECT("'"&List&"'!A1:A100"),A1,INDIRECT("'"&List&"'!C1:C100")))

BUT ... since your range size will change from time to time, you can create
a *dynamic range*, and use that instead.

From the Menu Bar, <Insert> <Name> <Define>
In the "Names In Workbook" box, type:
Mstrlist

Then, *change* whatever's in the "Refers To" box to:
=OFFSET(Resources!$C$5,0,0,COUNTA(Resources!$C5:$C45),1)

Then <OK>

Now, starting in C5 of the Resources sheet, as you enter *OR* remove sheet
names from the Mstrlist, this formula should work, without any revisions:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Mstrlist&"'!A1:A100"),A1,INDIRECT("'"&Mstrlist&"'!C1:C100")))