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")
Formula bar displaying TEXT formula, with D2 in the Month column showing 2025-11
D2 reads the payment date from the same row and returns 2025-11.

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.

PivotTable button on the Insert ribbon, with the worksheet below including the Month column
The source range includes the Month column.

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.

PivotTable Fields pane with Rows set to Month and Values set to Sum of Amount
Rows groups by Month, and Values calculates Sum of Amount.

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.

PivotTable showing amounts across four months and a Grand Total of 415
November 2025 and November 2026 each occupy their own row.

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.

Home ribbon displaying Cue AI button, with the Cue AI panel open on the left
Click Cue AI on the Home ribbon to open the left panel.
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.

Reopened workbook with column D filled with months, while columns A through C retain payment dates, activities, and amounts
The Month column is still in the workbook after reopening.

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
Ten registration records alongside the monthly PivotTable
Registration records span two years and include Novembers from different years.

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.

Download Cue Office for free