L
L
lockiz2021-08-01 00:54:35
Google Sheets
lockiz, 2021-08-01 00:54:35

How to withdraw (income) - expenses by month?

There are 3 sheets in the table. The first sheet where data arrives with the date and amount of the purchase. On the second sheet are the expenses with the date. And on the 3rd sheet you need to display net profit. 6105c5d60fcd4861769702.pngI managed to find a formula that counts by months =QUERY(ARRAYFORMULA({TEXT(A2:A;"YYYY-MMmm")\F2:F});"select Col1,sum(Col2) where Col1<> '2021-12' " &"group by Col1 label Col1'Month',sum(Col2)'Sum'") But I don't understand how to subtract from sheet 2... 6105c603e588f038798362.pngIs there a solution at all? To be automatically considered? I need to make it so that a net result would be shown for a month

Answer the question

In order to leave comments, you need to log in

1 answer(s)
A
Alexander Ivanov, 2021-08-02
@lockiz

Try something like this

=INDEX(
  QUERY(
    {
      'Заказы'!H3:I;
      {'Заказы'!M3:M\-1*'Заказы'!N3:N}
    };
    "select Col1, sum(Col2)
       where Col1<>'' and Col1<>'1899-12'
         group by Col1
           label Col1'Дата',sum(Col2)'Прибыль'"
  )
)

6107e4618ffb6620425742.png
Note that I have inverted the expense values ​​to negative ones -1*'Заказы'!N3:N.
I slightly corrected the formatting of the groups so that the output was the same and calculations were possible in the context of periods, so here is my copy of the Spreadsheet https://docs.google.com/spreadsheets/d/1y70jvzUVJT...

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question