06-19 Tips and Tricks for Google Forms for Summer Reading Tracking

Tips and Tricks for Google Forms for
Summer Reading Tracking
May 2019


Libraries track summer reading in so many ways, that we can't give instructions to each and every format here. If you need help setting up something that isn't addressed here, email marcia.literati@tenafly.bccls.org. We will follow up with a Tech Talk in early August to help you tally the data you've collected.

Data Collection

You can use google forms to simply track the number of people signing up for summer reading. The form responses will generate a spreadsheet which you can use to track data. Here are two examples: 

Once the names have been added to the spreadsheet you can simply add columns to the spreadsheet to “hand enter” data.  Alternately you can use a google form to enter data and then automatically total data categories.

In either case,

  1. Think about what data you will need at the end of the program. Your end-of-summer data analysis needs should dictate how you set up your google form.

  2. Collect the fewest number of data categories as possible. For example, if you only really need to know the total number of children, teens and adults that participate, create one multiple-choice question with 3 answers only and don't ask other questions such as school or grade.

    Do you need someone's phone number and email address or can you simply enter a library barcode number?  Do you really need any of this information?

    Example:  Use multiple choice question - Age group
    ◯ Children (ages 0 - 12)
    ◯ Teen (ages 13-18)
    ◯ Adult (over 18)

  1. Try to limit the number of fields in which you enter free form text. Instead use multiple choice questions whenever possible.

    1. For example, if you require a name entry, google will recognize MARCIA, marcia, Marcia as different values.

    2. From the standpoint of data collection, assigning a unique number to each participant limits possible data entry errors (of course you then need a way to keep track of the numbers you've assigned.)

    3. A library card number (if all readers have one) might be a good option. This also gives you the advantage of being able to scan the barcode which creates a smaller number of data entry errors than typing.

    4. If you require the entry to be a number and use response validation, you can limit some errors (see question 9 in sample google form).

      1. For example, if you track minutes but count everything under 15 minutes as 15 minutes, use “number”, “greater than or equal to” 15 to validate your data.


Data Analysis (use the spreadsheet generated by your google form)


You can manually enter additional data into the spreadsheet you originally generated when participants signed up.

For example, you can manually add a column for “# books read” and you simply enter the total at the end of the program. Or add a column for each week of the program and enter books read as reported by the participants, in each column.

Or you can use functions to automate the data analysis.

simple sum or total:  


  • A1 is the beginning of the data in the column you want to sum

  • A10 is the end of the data in the column you want to sum

Sum of values with a specific criteria (example: the total of all books read by grade)

=sumif(A1:A10, “Kindergarten”, B1:B10)

  • A1:A10 represents the column in which you identify the grade

  • the word in parenthesis is the actual value

  • B1:B10 represents the column in which has the number of books entered

Sum of values with 2 specific criteria (example: the total of all books read by grade for each school)

=sumifs(E2:E23, C2:C23,"Kindergarten", D2:D23, "Mackay")

  • E2:E23 represents the column that you are summing

  • C2:C23 represents the first criteria

    • (in this example you would enter each grade in parenthesis)

  • D2:D23 represents the second criteria

    • (in this example you would enter each school in parenthesis)

When you copy/paste formulas into new cells, google figures that you want the range of values as shown by A1:A10 to change. For example, if A1:A10 total is in cell B11 and I then copy the formula to cell B12, google will think it should total up the sum from cells A2:A11.There are times when you don't want the values to change.

If you want the values to be the same no matter where you paste the information use

A$1:A$10 to keep the rows from changing when you copy to another cell

Count all values:

=CountA (A1:A10)

  • A1 is the beginning of the column you want to count and A10 is the end

  • Note: this just counts the total number of entries but does not add up all the entries. Use SUM if you want to add.

List of unique values:

=Unique (A1:A10)

  • A1 is the beginning of the data in the column for which you want to find unique values

  • A10 is the end of the data in the column for which you want to find unique values

Use Query to find totals by unique values.

=query(A1:B22 , "select A, sum (B) group by A")

  • A1:B22 represents the entire list of values

  • ex: library card barcode number is in column A and number of books read is in column B


  • Select A = tells google to look for values in column A

  • Sum (B) = tells google add up the values in column B

  • Group by A = show me the totals from column B for each unique value in column A

Posted - Mon, May 13, 2019 at 8:51 AM. This article has been viewed 343 times.
Online URL: http://bccls.knowledgebase.co/article.php?id=1978

Powered by PHPKB (Knowledge Base Software)