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
    Commented Aug 28, 2012 at 2:40
  • ... or just view-share an worksheet.
    – Oneide
    Commented Aug 29, 2012 at 0:12
  • Could you give us some feedback please, whether the answers give were OK?
    – Jacob Jan
    Commented Jan 23, 2013 at 20:33
  • @Habba: your on Stack Overflow, so could you look at the answers we've come up with?
    – Jacob Jan
    Commented Mar 1, 2013 at 12:46

2 Answers 2


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)


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.