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.

"Domenic" wrote:

Try...

I1, copied down:

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

or

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

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

Hope this helps!

In article ,
Todd Lietha wrote:

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!