How to Sum Amounts by Month in an Excel PivotTable
To sum amounts by month in Cue Office, add a year-month column to your Excel data. In the PivotTable, put that column in Rows and set Values to sum the amounts. Including the year keeps November 2025 separate from November 2026.
Add the year and month to each payment
Open your Excel workbook (.xlsx) in Cue Office. In this example, the Registrations sheet lists community event payments. Column A contains payment dates, and column C contains amounts. Type Month in D1, select D2 in the grid, and paste the ten formulas below in one go.
=TEXT(A2,"yyyy-mm") =TEXT(A3,"yyyy-mm") =TEXT(A4,"yyyy-mm") =TEXT(A5,"yyyy-mm") =TEXT(A6,"yyyy-mm") =TEXT(A7,"yyyy-mm") =TEXT(A8,"yyyy-mm") =TEXT(A9,"yyyy-mm") =TEXT(A10,"yyyy-mm") =TEXT(A11,"yyyy-mm")
D2 pulls the date from A2, and the next row pulls from A3. yyyy-mm preserves a four-digit year and a two-digit month. When using your own data, verify that payment dates are stored as date values before applying the formula across every payment.
Use Month for rows and sum the amounts
Select A1:D11, including the headers and the new Month column. On the Insert tab, select PivotTable. In Create PivotTable, check the Source range. If it is incorrect, return to the sheet and select the range again. Enter the blank cell F1 in Destination cell and click Create.
After opening PivotTable Fields on the right, uncheck Paid date, check Month instead, and keep Amount. In the lower section, confirm that Rows is Month and Values is Sum of Amount. If Paid date remains in Rows, payments in the same month will be split across different dates.
Check both November totals
Save and reopen the workbook, then locate the two November entries in F1:G6. November 2025 has two payments of 45 and 30, which should total 75. November 2026 has two payments of 35 and 55, which should total 90. Each monthly total should appear on its own row.
December 2025 and January 2026 should each show 125. The Grand Total should be 415. If Values shows Count of Amount, the PivotTable is counting transactions. Check that the amounts are numbers, then check the value field’s calculation setting.
Have Cue AI fill the Month column
For a long list of payments, have Cue AI fill the Month column. Open the original workbook, select Cue AI on the Home tab, and send this prompt.
Can you add a Month column to the Registrations sheet? Put the header in D1 and fill D2:D11 with the year and month of each payment in column A, shown as yyyy-mm. Make each month update automatically if I change its payment date. Please leave the original data and the rest of the workbook as they are.
After Cue AI writes the formulas, save and reopen the workbook. Select D2 and check that the formula bar shows a reference to A2. Check the rest of D2:D11 against the payment dates, then select A1:D11 and follow the same steps to create the monthly PivotTable.
Practice with ten registration records
Expand registration records
Paste the three columns below into A1 of Registrations, verify that dates are date values and amounts are numbers, then add the Month column.
Paid date Activity Amount 2025-11-26 Repair workshop 45 2025-11-30 Bike clinic 30 2025-12-01 Bike clinic 25 2025-12-19 Repair workshop 80 2025-12-31 Tool orientation 20 2026-01-01 Bike clinic 60 2026-01-08 Tool orientation 15 2026-01-15 Repair workshop 50 2026-11-01 Bike clinic 35 2026-11-30 Tool orientation 55
Recheck the totals after adding late payments
A few registration payments may arrive after you’ve run the month-end totals. Add those payments at the end of the worksheet and ask Cue AI to fill Month for the new rows only. Spot-check a payment near month-end, then build the PivotTable from the range that includes the late payments.
When reconciling the month’s payments, use Cue Office to edit the Excel (.xlsx) workbook and open PDF (.pdf) receipts for free. For the handoff, you can note why late payments were added in a Word (.docx) document or show the revised monthly totals in a PowerPoint (.pptx) update.