Search code examples
google-sheetsgoogle-sheets-formula

Adding 2 computed columns to the results from a query in Google Sheets

I nearly have what I want working but not quite there! I have a Google sheet with 3 columns Date, Customer and Total I have written a formula that groups each line that has the same date and customer and totals the totals, it also adds 2 additional Columns to the result set.

What I'm trying to do now is to add two computed columns to the output of the query. The first column will be an amount based on the text in B i.e. if place 1 amount will be £10 if place 2 the amount will be £20 and finally if B is place 3 the amount would be £30. Then the last computed column would be the value in C minus the first computed column for that row.

In the sample linked below I have sample data and the current output as well as a sheet with what I would like the output put to be.

I'm trying to do this within the one query formula.

my current formula is :

=QUERY(Data!A:C, 
"SELECT A, B, SUM(C), 'Travel', 'Profit' 
WHERE 
A IS NOT NULL 
GROUP BY A, B 
ORDER BY A ASC 
label A 'Date', B 'Customer', SUM(C) 'Total', 'Travel' 'Travel', 'Profit' 'Profit'
")

Data

Data

Current Results

Current results

Results wanted

Results wanted

Sample sheet here https://docs.google.com/spreadsheets/d/1W_MKivwaT1gb1YgyV0lx4fzaJ81AVkW5UxOC3_OXYH4/edit?usp=sharing

I Have tried various things but they all end in errors my last attempt was:

=QUERY(Data!A:C, 
"SELECT A, B, SUM(C), 
IF(B = 'Place 1', '£10', IF(B = 'Place 2', '£20', IF(B = 'Place 3', '£30', ''))) AS 'Travel', 
'Profit' 
WHERE A IS NOT NULL 
GROUP BY A, B 
ORDER BY A ASC 
LABEL A 'Date', B 'Customer', SUM(C) 'Total', 
IF(B = 'Place 1', '£10', IF(B = 'Place 2', '£20', IF(B = 'Place 3', '£30', ''))) 'Travel', 
'Profit' 'Profit'")
Solution

  • You may try:

    =query({A:C,index(xlookup(B:B,K:K,L:L,))},"select Col1, Col2, sum(Col3), max(Col4), sum(Col3)-max(Col4) where Col2<>'' group by Col1, Col2")

    enter image description here