Partial sum of columns based on row identifier

Question

I have a spreadsheet with list of persons ("players"), and for a each person I have following values:

• ID number (unique)
• Name (just text)
• Score from round 1
• Score from round 2
• ...
• Score from round 16

Now, I have this other spreadsheet I want to use for displaying results. Each row has these columns:

• ID number
• Name (fetched using ID from the Players-sheet)
• First Round Eligible
• Last Round Eligible
• Score (sum of all rounds that are between First Round and Last Round).

I have problems calculating the score. Example: I have ID "17", First Round "3" and Last Round "11". This means I need to to calculate sum of score through rounds 3 to 11 for player with ID 17. I can use vlookup to fecth the Name based on ID, but I don't know how to apply First & Last round to sum calculation.

Another example would be using the following chart to calculate how many points the driver X has gained between Grand Prixes Y and Z. http://en.wikipedia.org/wiki/2012_Formula_One_season#Drivers.27_standings

Answer 1

The function VLOOKUP is only usable within a spreadsheet. This means that you use sheets !! Therefore I created a sample, based on sheets.

If you want to use spreadsheets however, then study the IMPORT function or Google Apps Script.

Remarks to the example:

1. I've created a table like the wiki page you included, with the calculated total. You can hide this sheet and lock it for updates, for anyone but you.
2. Pay attention to sheet 2, cell A11.
3. I've hidden rows 1 to 10 to make the autocomplete function to work.
4. See B1 in sheet 2, how it's done.

File I prepared with working example

Adding a sample of you own, helps a great deal to better understand what you mean !!

UPDATE: I've added edit rights to the example file I created, so you can play with it. Changed the names as well, each now being different (makes the auto-complete function work)

Answer 2

I made small example with ID, Name, Rounds 1-4. Entered data with 3 names.

• B8 is field to enter ID.
• B9 name lookup: =VLOOKUP(B8,A2:B4, 2)
• B10 field to enter first round
• B11 field to enter last round

First I made helper column G2:G4 consisting =(B2=$B$9) (copy down from G2 to G4).

Another helper row C12:F12 consisting =AND(C1>=$B$10, C1<=$B$11) (copy right C12 -> F12)

And finally the score: =SUM(FILTER(FILTER(C2:F4,G2:G4),C12:F12))

I believe there is a way to calculate without helper columns, but that's quick and dirty solution. :o)