Subtract values in a list that fit certain criteria within Google Sheets

Question

I've got this sheet where I do some basic accounting, and I want to be able to input the balance in my pocket every once in a while. The idea is that the expenses I record until the next balance will be subtracting from the latest amount recorded (through a form).

In order to achieve this, I'm attempting the following line:

=index(FILTER(J:J,not(ISBLANK(J:J))),rows(filter(J:J,not(ISBLANK(J:J)))),1)-dsum('Respostes hipotetiques'!$A:$E,"Quantitat",{"Cash?","Data de registre";"",">index(FILTER(i:i,not(ISBLANK(i:i))),rows(filter(i:i,not(ISBLANK(i:i)))),1)"})

J:J contains the list of balances, and that part works. The problem lies in the dsum statement.

"Respostes hipotetiques" is a sheet that holds some fake data, and the amount is recorded in Quantitat. Cash? is either blank or "n" for not cash (I don't want to take a bank transaction into account to update my pocket money), and the "Data de registre" holds the automatic timestamp from the input form for each record of expense.

Lastly, I:I is where the timestamp is held for the fake data.

I've been experimenting with several combinations, and it seems I can't make any operations with cell references in the dsum criteria. If this is so, is there any way to achieve the intent of what I wrote?

EDIT 12/05/2015

Just to clarify, a verbose description of the thought process the formula is trying to capture is this:

If I had 20 euros/dollars/whatever yesterday at 3pm, subtract only those cash transactions that occurred after yesterday at 3pm.

Answer 1

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 |        |       |
+---------+------------+--------+-------+