How to Calculate Annual and Year-to-Date Account Credits from Monthly Records in Excel
Question details
The user needs to summarize recurring monthly account payment records into calendar-year and year-to-date credit totals by account.
- Product
- Microsoft Excel 365 / WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A table contains monthly account changes and payment values across multiple account columns, and the user wants aggregated annual and year-to-date credit summaries.
- Observed behavior
- The source table is in a cross-tab layout, so annual and year-to-date credits are not directly visible without unpivoting or formula-based aggregation.
Make sure your monthly source table uses real date values in one column and that each account column contains only payment amounts. If you are using Power Query, keep a copy of the original worksheet before you unpivot.
Unpivot Account Columns with Power Query and Summarize with a PivotTable
Best for messy cross-tab monthly records because it converts each account-date-payment combination into one row and then aggregates by year and account.
Power Query preserves the original table and creates a clean normalized data model. Once the data is unpivoted, PivotTable aggregation handles annual totals without complex formulas.
In WPS Spreadsheets, click any cell inside the account table, open the Data tab, and choose From Table/Range to load the selected range into Power Query.
In Power Query, select the Date column first, then Ctrl+click each account column such as AC01, AC02, AC03, and AC04. Right-click any selected account column and choose Unpivot Columns.
Rename the resulting Attribute column to Account and the Value column to Payment. Adjust data types to Date, Text, and Decimal Number if needed.
Click Close & Load on the Home tab to return the unpivoted rows to a new worksheet in WPS Spreadsheets.
Select the loaded table, go to Insert > PivotTable, and place Account and Year in Rows. Drag Payment into Values to summarize credits by account and calendar year.
Use SUMIFS or Dynamic Array Formulas to Summarize Credits by Account and Year
Use this when your data is already in a flat account-date-value layout or you prefer formula-driven results that update immediately.
Use WPS Spreadsheets to Summarize Annual and Year-to-Date Account Credits
WPS Spreadsheets can load your monthly account table, unpivot recurring payment columns, and create a PivotTable or SUMIFS summary that shows annual and year-to-date credits by account.
- 1. Open your Excel monthly account workbook: Launch WPS Spreadsheets, click Open, and select the .xlsx or .xls file that contains the monthly account payment table.
- 2. Unpivot the account columns: Click inside the table, open Data > From Table/Range, select the account columns, and choose Unpivot Columns.
- 3. Build the annual and year-to-date summary: Close & Load the result, then insert a PivotTable or use SUMIFS formulas with Year and Date criteria to return totals by account.
Frequently Asked Questions
How do I unpivot multiple account columns without losing the date column?
In Power Query, select the Date column first, then select the account columns, right-click one of the selected account columns, and choose Unpivot Columns. The Date column remains a separate row field.
Can I use SUMIFS to calculate year-to-date credits?
Yes. Use SUMIFS with the payment column, account column, and date criteria that start at January 1 of the target year and end at the current date or a selected reporting date.
Why do I see repeated account rows after unpivoting?
Unpivoting creates one row per original account column and date. Aggregate the unpivoted rows with a PivotTable or SUMIFS so each account and year appears once.
Does WPS Spreadsheets support Excel dynamic array formulas such as VSTACK or MAKEARRAY?
WPS Spreadsheets opens Excel files with high compatibility, but very new dynamic array functions may depend on your WPS version. For a stable summary, use Power Query and PivotTable or SUMIFS.
How do I summarize account credits by both account and calendar year in a PivotTable?
After loading the unpivoted data, insert a PivotTable, drag Account to Rows, add a Year field to Rows or Columns, and drag Payment to Values.
