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

formulasgoogle sheets

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.

Best Answer

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