WPS Spreadsheets: Create a Running Total for Yearly Transactions
Question details
The user needs a running total that summarizes transactions for a selected year and updates when new transaction rows are added.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Transaction dates and amounts are stored in spreadsheet columns, and the user wants to calculate the yearly total based on a year entered in a separate cell.
- Observed behavior
- The user needs a dynamic yearly total that includes all dates within the selected year and expands automatically as new transactions are entered.
Confirm that the transaction dates are recognized as real date values and that the amount column contains numbers. Place the year to summarize in a separate cell, such as A1, before entering the formula.
Use an Excel-compatible Table and SUMIFS for a dynamic yearly total
Use this method when new transactions will be added regularly and the total should expand automatically with the table.
A SUMIFS formula can filter the transaction table by a start date that is on or after January 1 of the selected year and an end date before January 1 of the following year. Table references allow newly added rows to be included automatically.
Select the transaction range, including the Date and Amount headers, then choose Insert > Table in WPS Spreadsheets and confirm that the table has headers.
In a separate cell such as A1, enter the four-digit year you want to summarize, for example 2025.
Select the cell for the running total and enter =SUMIFS(Table1[Amount],Table1[Date],">="&DATE(A1,1,1),Table1[Date],"<"&DATE(A1+1,1,1)), then press Enter.
Enter each new date and amount in the first blank row directly below the table. The table and SUMIFS result will expand to include the new row.
Calculate the yearly total from regular cell ranges
Use this alternative when the data is not formatted as a table or when you need a quick calculation over a fixed range.
Build Dynamic Yearly Transaction Totals in WPS Spreadsheets
WPS Spreadsheets lets you organize transactions in an Excel-compatible table and use SUMIFS with date boundaries to create a yearly total that updates as rows are added.
- 1. Format the data as a table: In WPS Spreadsheets, select the Date and Amount columns and choose Insert > Table.
- 2. Set the selected year: Enter the year to summarize in a cell such as A1.
- 3. Add the formula: In the total cell, enter =SUMIFS(Table1[Amount],Table1[Date],">="&DATE(A1,1,1),Table1[Date],"<"&DATE(A1+1,1,1)).
- 4. Keep entering transactions: Add new dates and amounts in the next blank table row to update the yearly total automatically.
Frequently Asked Questions
How do I total transactions for a specific year in WPS Spreadsheets?
Put the target year in A1 and use =SUMIFS(Table1[Amount],Table1[Date],">="&DATE(A1,1,1),Table1[Date],"<"&DATE(A1+1,1,1)). This includes dates from January 1 of the selected year through December 31.
Will the running total update when I add a new transaction?
Yes. When the Date and Amount data is formatted as a table, enter the new transaction in the first blank row directly below the table. The structured SUMIFS references will include the added row.
Why does the SUMIFS formula return zero?
Check that the Date column contains real date values rather than text and that the Amount column contains numbers. You can also test a date cell with a date format to confirm that WPS recognizes it as a date.
Can I use a normal cell range instead of a table?
Yes. For dates in B2:B1000, amounts in C2:C1000, and the year in A1, use =SUMIFS(C2:C1000,B2:B1000,">="&DATE(A1,1,1),B2:B1000,"<"&DATE(A1+1,1,1)). Expand the ranges when more rows are added.
How can I calculate totals for a different year?
Replace the value in the year cell, such as A1, with another four-digit year. The SUMIFS formula will recalculate using January 1 of that year and January 1 of the following year as its boundaries.
