-1

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
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.