Sumproduct pulling from 17 other sheets

[Berk21]

I just got one week working, not sure how i can pull from multiple sheets.

Sheets are week 1 : week 17

works:
=SUMPRODUCT(--('Week 1 '!$C$9:$C$18="name"),--('Week 1 '!$I$9:$I$18="L"))

doesnt work:
=SUMPRODUCT(--('Week 1 :[Week 17]Week 17'!C$9:$C$18="name"),--('Week 1 :[Week 17]Week 17'!I$9:$I$18="L"))

One other question i have is kind of simialr to this formula.
where "name" data is placed, how can i have it look and determine 7 specfic names placed in that same ceall?...Joe,John,Ben,Sam etc....

if an example is needed, let me know
Thanks in advance

 

[arthurbr]

Cross post at http://www.msofficeforums.com/excel/10733-sumproduct-formula-pull-info-multiple-sheets.html
http://www.excelforum.com/excel-gen...ell-sum-total-according-to-a-name-lookup.html

 

[Oeldere]

Why not everything added in 1 sheet en then use a pivot table?

 

[Aladin Akyurek]

I just got one week working, not sure how i can pull from multiple sheets.

Sheets are week 1 : week 17

works:
=SUMPRODUCT(--('Week 1 '!$C$9:$C$18="name"),--('Week 1 '!$I$9:$I$18="L"))

doesnt work:
=SUMPRODUCT(--('Week 1 :[Week 17]Week 17'!C$9:$C$18="name"),--('Week 1 :[Week 17]Week 17'!I$9:$I$18="L"))

One other question i have is kind of simialr to this formula.
where "name" data is placed, how can i have it look and determine 7 specfic names placed in that same ceall?...Joe,John,Ben,Sam etc....

if an example is needed, let me know
Thanks in advance

Try, with A2 housing a name of interest...

=SUMPRODUCT(
  COUNTIFS(INDIRECT("'Week "&ROW(INDIRECT("1:17"))&"'!C9:C18"),A2,
    INDIRECT("'Week "&ROW(INDIRECT("1:17"))&"'!I9:I18"),"L"))

which requires Excel 2007 or later.

 

[Berk21]

thanks for all the information! i will start testing and will follow up.

Im going to close do to another post i regretably posted, unaware of cross posting. Sorry.

Thanks again for these ideas.