Google Sheets Query – Summarize First and Last Value by User

google sheetsgoogle-sheets-query

:Edit -> added a working spreadsheet with the data https://docs.google.com/spreadsheets/d/1RNlZtvYZIpRF97u-7cOEGLS4my4OcLHD_1OJfDRCVCA/edit#gid=2100307022

I have a spreadsheet with a list of users, a value, and a date:

The Spreadsheet

And I would like to take the first and the last value of column B grouping by column A (the user) and shorted by column C (the date of the value) and subtract this values

John 42-106 (-64)

Arthur 47-113 (-66)

Ben 53-48 (5)

Daniel 125-125 (0)

Richard 46-121 (-75)

I'm new with the query and the most I found is a sum query, grouped correctly, but shorted by the value, not the date

=query(A:C,"Select A,B, sum(B) group by A,B")

wrong shorting

I tried to add the order by column C argument

=query(A:C,"Select A,B, sum(B) group by A,B order by C")

but it didn't work. #VALUE!

and, of course, I don't want a sum, I need a subtract

I can make a external cell with the subtract if the query only respond with the values in different columns (John | 42 | 106), but I need that the data became shorted by the date like (Ben | 53 | 48)…

What can I do?

Best Answer

In your sample data, the first and last values are also the smallest and largest per name. If that is the case with your real data as well, you can get those values like this:

=query(A1:C, "select A, min(B), max(B) where A is not null group by A", 1)

In your sample spreadsheet, the first and last values are also the earliest and latest by the date in column C. These formulas will give results that match the expected results you show:

=query(A1:C, "select A, toDate(min(C)), toDate(max(C)) where A is not null group by A", 0)

=arrayformula( if( isnumber(K3:K) * (K3:K = J3:J), 0, iferror( vlookup($I3:$I & J3:J, { $A1:$A & int($C1:$C), $B1:$B }, 2, false) ) ) )

=arrayformula( iferror( vlookup($I3:$I & K3:K, { $A1:$A & int($C1:$C), $B1:$B }, 2, false) ) )

=arrayformula( if( M3:M + L3:L, M3:M - L3:L, iferror(1/0) ) )

Related Topic