0

I have a spreadsheet of financial transactions. The Columns headers are (A) Date, (B) Category, (C) Subcategory, (D) Description, (E) Amount.

I have a separate budgeting spreadsheet that contains a table of average transaction amount by category per month and whose column 1 header is category and each subsequent column header is the month of the year, and each cell is the amount spent that month.

Using GROUP BY, if I use the following QUERY I can get a list of all transactions summarized by unique category:

=query('FinancialTransactions'!A1:E, "SELECT B, SUM(E) where E < 0 group by B ")

If I use the following QUERY I can get the same data grouped by category for a specific month:

=query('FinancialTransactions'!A1:E, "SELECT SUM(E) where A <= date '2022-01-31' AND A >= date '2022-01-01' AND E < 0 AND C = 'New York' AND B = 'Grocery' group by B label SUM(E) ''")

Ultimately, what I'd like to accomplish is dynamically create the unique list of categories in column 1 either using UNIQUE or inside a QUERY and then dynamically create month and amount for each month across, so that it looks like this over the span of a date range (ie twelve months):

A header Jan Feb Etc etc
Grocery 212.23 205.11 etc
Utility 405.19 400.44 etc

Im brand new to spread sheets and google sheets in general, but I do know SQL. Regardless, I feel like there is a more efficient way to do this so I'm not manually entering data into the budget sheet.

1 Answer 1

1

You can use QUERY with the PIVOT clause, like this:

=query(A1:E, "SELECT B, SUM(E) WHERE A is not null GROUP BY B PIVOT MONTH(A)+1",1)

enter image description here

1
  • Nice! Do you know what I can use to format the date so the column header is Jan, or Jan-YYYY?
    – user658182
    Commented Jul 4 at 14:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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