You can refer to cells in the DSUM criterion. The problem with your query that you put a function inside of a string instead of appending its result to a string. That is, instead of
">index(FILTER(i:i,not(ISBLANK(i:i))),rows(filter(i:i,not(ISBLANK(i:i)))),1)"
you need
">"&index(FILTER(i:i,not(ISBLANK(i:i))),rows(filter(i:i,not(ISBLANK(i:i)))),1)
One can also use CONCAT or CONCATENATE for building query strings.
Since your formula is a bit convoluted, I illustrate the above with a simpler example. The Total table of the column below calculates the total population of cities founded after the date specified in the Cutoff column. The formula for the Total is
=DSUM(A$1:B$4,"Population",{"Founded";">"&C2})
+---------+------------+--------+-------+
| Founded | Population | Cutoff | Total |
+---------+------------+--------+-------+
| 1234 | 200 | 1333 | 153 |
| 1568 | 130 | 1111 | 353 |
| 1890 | 23 | | |
+---------+------------+--------+-------+
One approach is to add a status column next to the data input: for example, in L10 it would be
=if(iserror(match(H10, H11:H, 0)), "current", "obsolete")
which returns "current" if no entry below the present row has the same email. Then your query can filter out the obsolete results.
The above does not work with arrayformula. I don't think there is an arrayformula-compatible approach, because the number of comparisons is quadratic in the size of array. Here is a script solution that auto-expands: put status(H10:H) in a new column, with the following function in a script.
function status(arr) {
flat = arr.reduce(function(a, b) { return a.concat(b); });
var output = [];
for (var i=0; i<flat.length; i++) {
if (flat[i] === '') {
output.push(['']);
}
else if (flat.slice(i+1).indexOf(flat[i]) === -1) {
output.push(['current']);
}
else {
output.push(['obsolete']);
}
}
return output;
}
The approach I took in this answer also works reasonably well here: use unique to get unique users (identified by emails), then filter rows by email, and use vlookup to pick the data just from the last row. The result will be a neat summary table that has only the latest data.
Using your example, I build such a table starting from the cell A20. In A20, I enter
=unique(H10:H)
obtaining the list of emails. Then in B20 I enter
=vlookup(999999, filter($G$10:$K, $H$10:$H=$A20), column()+1)
and extend this formula to the range B20:D. This is the result:
+-----------------------------+-----------------+--------+-----------------+
| peter.goedtkindt@school.edu | SpecializationB | 2016.2 | changed my mind |
| student1@school.edu | SpecializationB | 2016.1 | |
| studnent2@school.edu | SpecializationC | 2016.1 | |
+-----------------------------+-----------------+--------+-----------------+
(I don't know if you needed comments, but I included them anyway.) Using column()+1 as the last parameter of vlookup allows the formula to pick the relevant column, after vlookup with absurdly large search key 999999 zooms in on the last entry.
Best Answer
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
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
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.