1

In Google Sheets I have multiple positive and negative numeric data entries per each day. I want to create a table using the query function, such that I have the following rows: Date, Negative, Positive, Total.

Date        Value
28/07/2016  10
28/07/2016  -0.9
28/07/2016  -20
29/07/2016  -30
29/07/2016  -12
29/07/2016  18
29/07/2016  -3
30/07/2016  7
30/07/2016  7
30/07/2016  15
30/07/2016  -1
30/07/2016  7

It looks like I could do three formulas with query function:

=query(A2:B13,"select A, sum(B) where B < 0 group by A order by A label A 'Date', sum(B) 'Negative'", -1)
=query(A2:B13,"select A, sum(B) where B > 0 group by A order by A label A 'Date', sum(B) 'Positive'", -1)
=query(A2:B13,"select A, sum(B) group by A order by A label A 'Date', sum(B) 'Total'", -1)

but the problem is that I have to select Date every time to be able to group and order by it. If I do not select A, the function returns #VALUE!. So the result looks like this:

Date        Negative    Date        Positive    Date        Total
28/07/2016  -20.9       28/07/2016  10          28/07/2016  -10.9
29/07/2016  -45         29/07/2016  18          29/07/2016  -27
30/07/2016  -1          30/07/2016  36          30/07/2016  35

Can I somehow avoid selecting Date for Positive and Total? I know I can simply hide these columns, but is there another way?

Asu
  • 1,723
  • 2
  • 21
  • 32

2 Answers2

3

Try combining single formula with use of double query and arrays {}:

={query(A2:B13,"select A, sum(B) where B < 0 group by A order by A label A 'Date', sum(B) 'Negative'", -1), query(query(A2:B13,"select A, sum(B) where B > 0 group by A order by A label A 'Date', sum(B) 'Positive'", -1),"select Col2"), query(query(A2:B13,"select A, sum(B) group by A order by A label A 'Date', sum(B) 'Total'", -1),"select Col2")}

This construction: query(query(...),"select Col2") will select only the second column of data.

and {data1, data2, data3} makes data2, data3 to be placed to the right in a new column.

Max Makhrov
  • 17,309
  • 5
  • 55
  • 81
  • You Sir, are a genious! I hadn't realised nested queries were even possible with Sheets. – Asu Aug 01 '16 at 10:17
1

I don't know if it is any easier, but you could create a column C with =if(B2>0,B2,"") and a column D with =if(B2<0,B2,"") then fill them down. Then use:

=query(A2:D13,"select A, sum(D),sum(C),sum(B) where A is not null group by A order by A label A 'Date', sum(D) 'Negative',sum(C)'Positive',sum(B) 'Total'", -1)
Ed Nelson
  • 10,015
  • 2
  • 27
  • 29