I have a dataset in Google Sheets like this:
| Purchase Date | Price |
|---|---|
| 9/24/2023 | $49.00 |
| 10/25/2023 | $29.00 |
| 1/4/2024 | $29.00 |
| 1/8/2024 | $39.00 |
| 1/31/2024 | $39.00 |
| 2/21/2024 | $29.00 |
| 3/13/2024 | $49.00 |
| 3/13/2024 | $39.00 |
| 3/13/2024 | $29.00 |
| 4/1/2024 | $69.00 |
| 4/7/2024 | $39.00 |
| 4/25/2024 | $39.00 |
| 4/26/2024 | $69.00 |
| 4/29/2024 | $39.00 |
| 5/5/2024 | $49.00 |
| 5/6/2024 | $29.00 |
| 5/7/2024 | $59.00 |
| 5/9/2024 | $39.00 |
| 5/20/2024 | $29.00 |
| 5/29/2024 | $29.00 |
- I want to summarize sales so that:
- The rows are the 3‑letter month names (Jan, Feb, etc.).
- The columns are years (e.g., 2023, 2024, 2025, etc).
- The cell values are the sum of sales for that month/year combination.
Result will be like this:
| Month | 2023 | 2024 | 2025 |
|---|---|---|---|
| Jan | $107.00 | $294.00 | |
| Feb | $29.00 | $107.00 | |
| Mar | $117.00 | $137.00 | |
| Apr | $255.00 | $88.00 | |
| May | $283.00 | $108.00 | |
| Jun | $226.00 | $391.00 | |
| Jul | $206.00 | $462.00 | |
| Aug | $59.00 | $412.00 | |
| Sep | $49.00 | $136.00 | |
| Oct | $29.00 | $49.00 | |
| Nov | $167.00 | ||
| Dec | $118.00 |
I can do this with a few formulas and a helper column. First, I change the dates into separate Month and Year columns. Then I copy the months and years and use SUMIFS to get the totals. This works, but I want a single formula that updates automatically when new years are added.
First I paste months and Year then I have used SUMIFS formula to sum the sales based on month and year
=SUMIFS('2. File'!$F:$F,'2. File'!$A:$A,C$3,'2. File'!$B:$B,$B4) Months and years are copy pasted whereas I want them to be Dynamic.
=QUERY({ARRAYFORMULA(TEXT(A2:A21, "mmm")),ARRAYFORMULA(TEXT(A2:A21, "yyyy")),A2:B21}, "SELECT Col1, SUM(Col4) GROUP BY Col1 PIVOT Col2 ORDER BY Col1 LABEL Col1 'Month'")but about sorting queries cannot be reliably sorted by month name alone (e.g., "January," "February") because text-based sorting is inherently alphabetical, not chronological.