# Automatically group data by month

I am using Google Sheets and I would like to display monthly in and out.

``````date        units    unit cost   cost
2015-01-05   200      0.24275   -48.55
2015-01-05     1      1.43       -1.43
2015-01-09     4      7.5       -30
2015-01-09    50      0.7852    -39.26
2015-01-09     1      0.82       -0.82
2015-01-14     1    275        -275
2015-01-20     1      1.24       -1.24
2015-01-23    10      0.099      -0.99
2015-01-24     1      6.99        6.99
2015-01-24     1      0.44       -0.44
2015-01-24     1      0.7        -0.7
2015-01-24     1      0.93       -0.93
2015-01-26     1      6.99        6.99
2015-01-26     1      0.44       -0.44
2015-01-26     1      0.73       -0.73
2015-01-27  1000      0.23798  -237.98
2015-01-26   200      0.00945    -1.89
2015-01-26   100      0.0495     -4.95
``````

I am using `=SUMIF(E2:E299,"<0")` for total outgoings and `=SUMIF(E2:E299,">0")` for total incomings.

How can I automate it to display every month?

updated

Using the following formula the month is not being displayed in order?

``````=query(ArrayFormula(if(len(Sheet1!B2:B), {text(Sheet1!B2:B, "mmm"),if(Sheet1!E2:E<0, Sheet1!E2:E,0), if(Sheet1!E2:E>0, Sheet1!E2:E,0)},)), "select Col1, sum(Col2), sum(Col3), sum(Col3) + sum(Col2) where Col1 <>'' group by Col1 label Col1 'Month', sum(Col2) 'Outgoing', sum(Col3) 'Incoming', sum(Col3) + sum(Col2) 'Gain'")

Month   Outgoing   Incoming    Gain
Feb     -105.02      66.71   -38.31
Jan     -650.37      34.95  -615.42
Jun     -143        139       -4
Mar     -179.79     263.34    83.55
``````
• If I had to guess, the non-standard English and poor formatting is not serving your question very well. Making a question hard to read doesn't invite people to help. – ale Jun 9 '15 at 20:21

Assuming your dates are in col A, try:

``````=query(ArrayFormula(if(len(A2:A), {text(A2:A, "mmm"),if(E2:E<0, E2:E,0), if(E2:E>0, E2:E,0)},)), "select Col1, sum(Col2), sum(Col3) where Col1 <>'' group by Col1 label Col1 'Month', sum(Col2) 'Outgoing', sum(Col3) 'Incoming'")
``````

or, if you want an extra column with the 'gain':

``````=query(ArrayFormula(if(len(A2:A), {text(A2:A, "mmm"),if(E2:E<0, E2:E,0), if(E2:E>0, E2:E,0)},)), "select Col1, sum(Col2), sum(Col3), sum(Col3) + sum(Col2) where Col1 <>'' group by Col1 label Col1 'Month', sum(Col2) 'Outgoing', sum(Col3) 'Incoming', sum(Col3) + sum(Col2) 'Gain'")
``````

If your data spans several years, you may want to change the formula to:

``````=query(ArrayFormula(if(len(A2:A), {text(A2:A, "mmm yyyy"),if(E2:E<0, E2:E,0), if(E2:E>0, E2:E,0)},)), "select Col1, sum(Col2), sum(Col3), sum(Col3) + sum(Col2) where Col1 <>'' group by Col1 label Col1 'Month', sum(Col2) 'Outgoing', sum(Col3) 'Incoming', sum(Col3) + sum(Col2) 'Gain'")
``````

EDIT: another way may be (formatting dates as MM-YYYY if that is okay)..

``````=query(ArrayFormula(if(len(A2:A), {text(A2:A, "MM-YYYY"), if(E2:E<0, E2:E,0), if(E2:E>0, E2:E,0)},)), "select Col1, sum(Col2), sum(Col3), sum(Col3) + sum(Col2) where Col1 <>'' group by Col1 order by Col1 label Col1'Month', sum(Col2) 'Outgoing', sum(Col3) 'Incoming', sum(Col3) + sum(Col2) 'Gain'")
``````
• i tried the 1st one i get an error. date column is B. so i changed your formula to use B – shorif2000 Jun 9 '15 at 15:48
• works. didn't realise i had 2x equals sign – shorif2000 Jun 9 '15 at 15:50
• will this keep consecutive years? it wont merge 2014 jan with 2015 jan? – shorif2000 Jun 9 '15 at 15:55
• i have updated question – shorif2000 Jun 9 '15 at 16:03
• How would your desired output be ? Month in a column and year in a column or rather mmm-yy in one column ? or... – JPV Jun 9 '15 at 18:12