I’ve been playing around with gamifying my classroom this semester. After looking at Classcraft I decided I wanted something a little simpler, so I put together a Google Sheet to keep track of XP, AP, and HP. The problem came when I needed to add or subtract a stat from a student.

Entering the data

A Google Form was going to be the easiest, so I created a form to enter XP, AP, and HP. The first item asked for the name of the student, then a reason, and finally, the number of points. This went into a sheet in Google Sheets.

But how do I add up Tom’s total number of points he spent?

Adding them up

For this example, the above sheet is named AP, and I have another sheet with the totals for AP. That sheet looks like this:

The magic happens with the formula that is in B2:B5. The formula is:

=if(iserror(VLOOKUP(A2,AP!$B$2:$D$523,3,FALSE)),0,sumif(AP!$B$2:$B$523,A2,AP!$D$2:$D$523))

That looks like a mess, but lets break it down.

=if(iserror(VLOOKUP(A2,AP!$B$2:$D$523,3,FALSE)),0

An if statement has three parameters, the first two are above. The first parameter is the ISERROR function. This function uses the VLOOKUP function to let us know if a student’s name is actually returned in the form. Without checking for this, every student that has not used any AP will get a nice #N/A instead of a 0. If the first parameter returns true, which it would if the name isn’t sheet, then return a 0.

sumif(AP!$B$2:$B$523,A2,AP!$D$2:$D$523))

If the first parameter returns false, then the sumif function runs. It adds up the range AP!$D$2:$D$523 if the range AP!$B$2:$B$523 is equal to A2. And now anything entered to the AP sheet will automatically be reflected for the student totals.