Working in Google Sheets, I want to create a pivot table or some sort of separate tab/sheet that will automatically keep a running tally of all dollar amounts by month.
I've read these 3 questions:
They lead me to believe I could write formulas to filter & sum by date. But I think I'd have to keep adding new month-rows as my original table grows...right?
Here's my table:
| Date | Amount | Date | Amount | Date | Amount |
|---|---|---|---|---|---|
| Jan, 2024 | $1,490.83 | Feb, 2024 | $372.71 | Mar, 2024 | $372.71 |
| Jan, 2024 | $1,385.83 | Feb, 2024 | $346.46 | May, 2024 | $346.46 |
| Mar, 2024 | $75.00 | Apr, 2024 | $18.75 | May, 2024 | $18.75 |
| Mar, 2024 | $150.00 | Apr, 2024 | $37.50 | May, 2024 | $37.50 |
| Mar, 2024 | $1,500.00 | Apr, 2024 | $375.00 | May, 2024 | $375.00 |
Hoping to automatically output...
| Month | Total |
|---|---|
| Jan, 2024 | $2,876.67 |
| Feb, 2024 | $719.17 |
| Mar, 2024 | $2,444.17 |
| Apr, 2024 | $431.25 |
| May, 2024 | $431.25 |
I tried to create a pivot table, but I'm not good at them and it just started listing all the months again, without aggregating their dollar values.
The layout of the original table is not editable, so I can’t align or order the months.
Is this possible?
TL;DR
Suppose you copy your example sheet to column A:F
In H1, enter
={"Date";unique(flatten(filter(A2:F,A1:F1="Date")))}
and then in I1, enter
={"Total";map(H2:H,lambda(c,if(isblank(c),,sum(filter(flatten(filter(A2:F,A1:F1="Amount")),flatten(filter(A2:F,A1:F1="Date"))=c)))))}
You can "cut" both cells together and "paste" in any location you need -- but they should be outside your data columns, ie. A:F.
Explanations
To get the unique months under one date column, you can use unique. For example,
=unique(A2:A)
Since your data entries repeat across multiple columns (which I do advise against), you can aggregate all dates using filter and flatten. For example,
=flatten(filter(A2:F,A1:F1="Date"))
In the above, the implicit assumption from your sheet is that the date columns always have a header cell containing exactly the text "Date".
Thus, if you put in cell H1
unique(flatten(filter(A2:F,A1:F1="Date")))
you will get in column H a column of dates in the format of month-name, year, and the adherence to this format of dates is another implicit assumption from your sheet.
Note that the range for the data entries is A2:F, which does not specify the end row. That is intentional and it allows you to grow your data tab without changing the formula. (Again, I advise growing the data tab vertically.)
Assuming your "Amount" columns always come with a header cell containing the text "Amount", you can then use the same functions to aggregate all the dollar amount:
flatten(filter(A2:F,A1:F1="Amount"))
Thus, altogether, you can use filter on the "Amount" cells based on "Date" cells. For example, in cell I1, you may enter,
=sum(filter(flatten(filter(A2:F,A1:F1="Amount")),flatten(filter(A2:F,A1:F1="Date"))=H1))
and the resulting figure is the total amount for Jan, 2024.
Note that the implicit assumption from your sheet is that your data always has Date-Amount pairings and nothing else. Amount can be zero or empty of course. But the format is adhered to.
At this point, you can "drag" the formula down if you wish and complete the task at hand.
You can also centralize the formulas to one single cell using map and enter in cell I1 the following formula:
=map(H1:H,lambda(c,if(isblank(c),,sum(filter(flatten(filter(A2:F,A1:F1="Amount")),flatten(filter(A2:F,A1:F1="Date"))=c)))))
where the range H1:H does not have an end row, similar to earlier, which allows for growing your data tab. In order to allow for the lack of end row, if(isblank(),... is used to ignore empty rows. This particular position of if(isblank() within the lambda function is what I have experimented and found to be efficient with Google Sheet. Earlier we didn't need it because filter already detects end row.
And you completed the task at hand. In the TL;DR answer section, I added header cells that you specified in your example output. Using the array concatenation syntax {...;...}.
Note how all original formatting is preserved in the newly created column H and column I. That is a feature of using these built-in functions.