Sum VLOOKUP in multiple ranges

[Neorth]

Hi,

I have a spreadsheet containing a tournament result tables where each years tournament get it's own worksheet (named 2014, 2015, ...). I also have a "Hall of fame" worksheet that sums wins, losses, goals made, etc. for all years but I can't get that part to work. The following formula finds the goals made by a team in 2014:

=VLOOKUP('Hall of fame'!$A2;'2014'!$B$2:$J$7;6;) // The A-column contains the name of the team that is also found in column B of the tables sheets.

To sum the goals I tried this but it gives me the same result as the one above:

{=SUM(VLOOKUP('Hall of fame'!$A2;INDIRECT("'"&Years&"'!$B$2:$J$7");6;))} // Years is a named range of years (would be nice to be able to get that by available worksheets)

Is it possible to do in some other way?

Thanks in advance!

Kind regards
Joakim

[Glenn Kennedy]

Hi. Please read the yellow banner, at the top of the page, and post a sample sheet.

[Neorth]

Sorry, missed to upload the example. Now it's there!

[menem]

Please try

=SUMPRODUCT(SUMIF(INDIRECT("'"&Years&"'!B:B"),$A2,INDIRECT("'"&Years&"'!G:G")))

Regards.

[Neorth]

Thank you! Work like a charm!