# Partial sum of columns based on row identifier

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

-
Please, post some examples (images of the tables as you are describing). – Oneide Aug 28 '12 at 2:40
... or just view-share an worksheet. – Oneide Aug 29 '12 at 0:12
Could you give us some feedback please, whether the answers give were OK? – Jacob Jan Tuinstra Jan 23 '13 at 20:33
@Habba: your on Stack Overflow, so could you look at the answers we've come up with? – Jacob Jan Tuinstra Mar 1 '13 at 12:46

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)

-

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)

-