Sumproduct pulling from 17 other sheets

Berk21 said:

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

Click to expand...

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