-1

I have three tabs:

  1. Transactions
  2. Payees
  3. Categories

This is what my Transactions tab looks like:

+--------------+-------------+-----------+
|         Date |       Payee |    Amount |
+--------------+-------------+-----------+
|   29/03/2020 |   Company 1 |   $100.00 |
+----------------------------------------+
|   27/02/2020 |   Company 1 |   $220.00 |
+----------------------------------------+
|   12/02/2020 |   Company 2 |   $350.00 |
+----------------------------------------+
|   05/01/2020 |   Company 2 |    $50.00 |
+----------------------------------------+
|   28/03/2020 |   Company 1 |   $125.00 |
+--------------+-------------+-----------+

This is what my Payees tab looks like:

+-------------+------------+
|       Payee |   Category |
+-------------+------------+
|   Company 1 |        Gas |
+--------------------------+
|   Company 2 |  Groceries |
+-------------+------------+

As you can see, each company is associated with a category.

This is what my Categories tab looks like:

+-------------+-----------+-----------+-----------+
|    Category |   03/2020 |   02/2020 |   01/2020 |
+-------------+-----------+-----------+-----------+
|         Gas |           |           |           |
+-------------------------------------------------+
|   Groceries |           |           |           |
+-------------+-----------+-----------+-----------+

As you can see in my Categories tab, there are empty columns for the months. What I want to do is fill these columns by aggregating total amounts by category.

In the end, the Categories tab should look like this:

+-------------+-----------+-----------+-----------+
|    Category |   03/2020 |   02/2020 |   01/2020 |
+-------------+-----------+-----------+-----------+
|         Gas |   $225.00 |   $220.00 |        $0 |
+-------------------------------------------------+
|   Groceries |        $0 |   $350.00 |    $50.00 |
+-------------+-----------+-----------+-----------+

How would I go about combining this data?

And additionally, is it possible to create a new column in the Categories tab if a new transaction is added to the Transactions tab for a new month, e.g. 04/2020?

  • 1
    Welcome. Please follow the tour, checkout How to Ask and add a brief description of your search/research efforts. Also edit the question lo limit to only one question. – Rubén Mar 29 at 23:49
  • 1
    Welcome. Have you made your own an attempt to combine this data? Include that in your question. – Tedinoz Mar 31 at 4:57
  • Honestly, I've done a full day of research and digging around this site for answers, and either I don't know what to search for, or I do, and I can't figure it out. :/ – user698515 Apr 1 at 1:28
  • Does this answer your question? How to group data by month in Google Sheets – Tedinoz Apr 1 at 4:01
  • @Tedinoz No, it does not. I have an additional tab that complicates the calculations in your linked question. – user698515 Apr 1 at 4:27
1

You want to create monthly totals by supply type for transactions. Each payee supplies only one supply type.

There may be several ways to resolve this problem. This answer identifies two methods; it is probable that there are more efficient methods of generating this data, however these at least resolve the problem.


Query: one query per month per supply type.

=iferror(query({query(Transactions!$A$2:$C$6,"Select A, B, C"), arrayformula(vlookup(query(Transactions!$A$2:$C$6,"Select B"), Payees!$A$2:$B$3,2))}, "select Sum(Col3) where Col1 >= date '"&TEXT(B$1,"yyyy-MM-dd")&"' and Col1 <= date '"&TEXT(eomonth(B$1,0),"yyyy-MM-dd")&"' and Col4= '"&$A2&"' label Sum(Col3) ''"),0)

This formula is entered in cell B2 and is copied into each of the columns and rows.

The formula assumes that dates in the column headers are the 1st day of each respective month. Transaction dates are evaluated against this date, and against the eomonth.

Screenshot


Pivot Table

The data for the pivot table comes from this formula:
=query({arrayformula(month(Transactions!A2:A6)), query(Transactions!A2:C6,"Select A, B, C"), arrayformula(vlookup(query(Transactions!A2:C6,"Select B"), Payees!A2:B3,2))},"select Col5, Col1, Sum(Col4) group by Col5, Col1 order by Col1 desc label Col5 'Type',Col1 'Month', Sum(Col4) 'Total'")

This query uses a arrayformula where month is calculated for the Transaction dates. The output looks like this:

Pivot Source


Then the pivot table is created manually:

Pivot Table

| improve this answer | |
  • It looks like I still have to do some work manually. I was hoping I would just be able to add new transactions and it could do the work for me, like through scripts or something. Is that possible or out of scope for Sheets? – user698515 Apr 1 at 21:54
  • Query based option: change the formula to =iferror(query({query(Transactions!$A$2:$C,"Select A, B, C"), arrayformula(vlookup(query(Transactions!$A$2:$C,"Select B"), Payees!$A$2:$B$3,2))}, "select Sum(Col3) where Col1 >= date '"&TEXT(B$1,"yyyy-MM-dd")&"' and Col1 <= date '"&TEXT(eomonth(B$1,0),"yyyy-MM-dd")&"' and Col4= '"&$A2&"' label Sum(Col3) ''"),0). "Transactions range" is now open-ended and you can add as many transactions as you like. To add extra months, insert new columns to left of March, ensure Column header is first day of the month, and copy formula into the new cells. – Tedinoz Apr 1 at 23:38
  • You could actually set this up 12 months in advance, and hide the "Months" that you don't want to see yet. – Tedinoz Apr 1 at 23:39
  • Pivot option: change formula to: =query({arrayformula(month(Transactions!A2:A)), query(Transactions!A2:C,"Select A, B, C"), arrayformula(vlookup(query(Transactions!A2:C,"Select B"), Payees!A2:B3,2))},"select Col5, Col1, Sum(Col4) where Col2 is not null group by Col5, Col1,Col2 order by Col1 asc label Col5 'Type',Col1 'Month', Sum(Col4) 'Total'"). Same thing, Transaction Range is open-ended, not where statement to avoid empty dates.In Pivot Table editor, change range of pivot source to make it open-ended. Apart from that, Pivot Table should update automatically. – Tedinoz Apr 1 at 23:51
  • I'm having some trouble modifying your query-based formula. All of my tabs - Transactions, Payees, and Categories - have many more rows than in my question. I tried modifying your formula to allow for that, but I'm getting numbers that are far off. Any ideas? – user698515 Apr 2 at 1:44

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.