I’ve got a form I am working on that allows me to enter a company or “description” in a text field and search my transactions for all matches. I’d like to add a total to the lookup. I am currently returning, DAT, Desc, Category, Amount. a total for the amount would be great.
=QUERY(Transactions!B1:F,“SELECT * WHERE LOWER© CONTAINS LOWER(’” &G1& “’)”)
Anyone have thoughts on editing this formula to do that?
Hi @bkmccarty,
It looks like you are trying to return all the transactions with a certain Description in cell G1?
Some version of SQL Queries let you include a ROLLUP option to get column totals, but unfortunately Google Sheets doesn’t offer this.
One way to get a Total for the lookup is to put the query a few rows down from the top of the sheet. Then above the query row, you can use a formula like =SUM(D4:D) to get the total for the column you want. In that example, D4 is the first cell of the column you want to sum.
Would that work for you?
If you are trying to get the sum results for all possible Descriptions, then you need to use SUM(column letter of the Amount Field) in the first part of the Query and then GROUP BY [the column letter of the Description field. See @richl 's reply for an example of that.
Jon
Addign a total field isnt too hard. you can use something like this.
“SELECT Col4 ,SUM(Col5) WHERE Col4 IS NOT NULL GROUP BY Col4 LABEL SUM(Col5) ‘Total Balance’”
I am not to sure what your Where clause is trying to do, but it doesnt look right.
My WHERE clause is filtering on a typed in Description. For example in G1 I can enter the text microsoft and have the query return only transactions containing that description.
Looking for this list to contain every transaction, not grouped, and then just provide a sum total at the end.
For now I’ve just done as @jono suggested. I added a item next to my reference cell with a =sum(I5:I) to total all my line items.
This will work out fine for my purposes. Thank you.
Awesome. You could also approach this using an if statement.
Here is a theoretical formula.
If row = 1, value if true (sum amount query) , value if false (your existing query)
This would put the total on first row and details below.