What script / function would allow me to subtract a value from a unique account

Question

I have an item shop and bank account set up for my students in class using Sheets. Currently students purchase their items via Google Forms and it logs their order in a Sheet. Normally, I would find that student on the bank sheet and subtract from their account the total cost of their order, but I feel like there is a way that I can have google automate the process.

For example: Student1 spends $100.00 and then student27 spends $50.00

Is there a way for Sheets to find those unique individuals and then input a negative amount into their bank accounts?

Answer 1

There are many approaches. Here is one without a script. Say, the columns B and C are populated from the form, B has the name of a student, and C has the amount they paid. Then the query

=query("FormResponses!B:C", "select B, sum(C) group by B", 1)

will get the total amount spent by each student.

You can then subtract the query result from their bank balance. Say, the query output is in columns E:F of Sheet1. To get the amount spent by the student mentioned in some other cell H2, use

=iferror(vlookup(H2, E:F, 2, 0), 0)

which means: look for the name in H2 in the column E, and return the corresponding value from column F (2nd column of the searched range). If there is no match, 0 is returned as the student did not spend anything.