How to Use XLOOKUP and SUMPRODUCT for a Rolling Three-Month Summary in WPS Spreadsheets
Question details
The user needs to create a chart-ready summary that automatically includes only the latest three months of newly added data using XLOOKUP and SUMPRODUCT, without changing the PivotTable source each month.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Building a monthly summary from a data table with dates, categories, and amounts; the summary should update automatically as new rows are added.
- Observed behavior
- The user wants the summary range to evaluate only the current month and the two preceding months so charts refresh without manual source changes.
Prepare a data table with Date, Category, and Amount columns, and make sure the date column contains real date values instead of text. Decide whether the rolling period is based on today’s date or the latest date in the data.
Build a Dynamic Three-Month Total with SUMPRODUCT and Date Boundaries
Use this when you want category totals for a chart-ready summary that automatically rolls forward as the current month changes.
SUMPRODUCT can evaluate multiple conditions without entering an array formula. The date boundaries for the current month and the two preceding months are created with EOMONTH so the summary always covers the latest three months.
Click the empty summary cell where the first rolling total should appear in WPS Spreadsheets.
Type =SUMPRODUCT((DateRange>=EOMONTH(TODAY(),-3)+1)*(DateRange<=EOMONTH(TODAY(),0))*(CategoryRange=summary_category)*AmountRange), replacing DateRange, CategoryRange, AmountRange, and summary_category with your actual ranges and category cell.
If the workbook should roll from the latest date in the data, replace TODAY() with MAX(DateRange), for example =SUMPRODUCT((DateRange>=EOMONTH(MAX(DateRange),-3)+1)*(DateRange<=EOMONTH(MAX(DateRange),0))*(CategoryRange=summary_category)*AmountRange).
Fill the formula down for additional categories, keeping the date and amount ranges absolute with $ signs where needed.
Use XLOOKUP for Row-Level Lookups Inside the Rolling Window
Use this when you need to retrieve a specific matching row, label, or related amount within the rolling three-month period.
Build a Rolling Three-Month Summary with SUMPRODUCT and XLOOKUP in WPS Spreadsheets
WPS Spreadsheets supports XLOOKUP, SUMPRODUCT, EOMONTH, and date-based criteria, so you can create a dynamic rolling three-month summary that updates charts automatically. The workbook remains compatible with Microsoft Excel .xlsx files.
- 1. Open your data workbook in WPS Spreadsheets: Open the workbook that contains the date, category, and amount columns, and select the sheet with the raw data.
- 2. Define the summary table and date reference: Create a small summary area with category names and a reference cell for the current period, or use =TODAY() or =MAX(DateRange) directly in the formulas.
- 3. Enter the rolling SUMPRODUCT formula: In the first total cell, enter =SUMPRODUCT((DateRange>=EOMONTH(TODAY(),-3)+1)*(DateRange<=EOMONTH(TODAY(),0))*(CategoryRange=category_cell)*AmountRange).
- 4. Add XLOOKUP detail formulas if needed: In a detail cell, enter =XLOOKUP(1,(DateRange>=EOMONTH(TODAY(),-3)+1)*(DateRange<=EOMONTH(TODAY(),0))*(CategoryRange=category_cell),AmountRange,"Not found",0).
- 5. Insert a chart from the summary range: Select the fixed summary cells, go to Insert > Chart, choose the chart type, and the chart will use the summary range as its source.
Frequently Asked Questions
How do I make the rolling three-month formula use the latest data date instead of TODAY()?
Replace TODAY() with MAX(DateRange) in the formula. For example, use EOMONTH(MAX(DateRange),-3)+1 and EOMONTH(MAX(DateRange),0) as the start and end date boundaries.
Why does SUMPRODUCT return zero when the dates look correct?
The date column is likely stored as text. Check with =ISNUMBER(A2) or use Text to Columns to convert the column to real date values. Also make sure all ranges in the formula have the same size.
Can I use SUMIFS instead of SUMPRODUCT for the rolling summary?
Yes. You can use =SUMIFS(AmountRange,DateRange,">="&start_date,DateRange,"<="&end_date,CategoryRange,category_cell) with the same start and end dates. Use XLOOKUP only when you need a single matching row value.
What does XLOOKUP add if SUMPRODUCT already sums the amounts?
XLOOKUP retrieves the first matching detail row or associated value inside the rolling window. Use SUMPRODUCT or SUMIFS for totals, and XLOOKUP for row-level lookup, labels, or related fields.
How can I feed the rolling summary to charts without changing the source?
Build a fixed summary range with the formulas, select that range, and insert the chart from Insert > Chart. The chart source stays fixed while the formulas recalculate as new rows are added.
