Create a Dynamic Requester Summary with WPS Spreadsheets Formulas
Question details
The user needs to build a summary sheet from a master data sheet that lists unique requesters, counts authorized requests, and calculates total request costs.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user maintains a master data sheet of requesters and wants an analysis view that summarizes only authorized requests by requester.
- Observed behavior
- The desired output is a dynamic summary showing unique requesters with their authorized request counts and total costs.
Make sure your master data has consistent columns for requester name, request status, and request cost, with no blank requester cells inside the range. If you use the formula method, confirm that your WPS Office version supports UNIQUE and FILTER.
Create a formula-driven requester summary with UNIQUE, FILTER, COUNTIFS, and SUMIFS
Use this method when you want a live summary that refreshes automatically whenever the master data changes.
This method uses dynamic array formulas to extract only authorized requesters, then conditional counting and summing functions to summarize totals. It works best when your WPS Spreadsheets version supports UNIQUE and FILTER.
Open WPS Spreadsheets, click any cell in the master data, go to Insert > Table, confirm the range, and name the table MasterTable under Table Tools.
On a new sheet, select the first output cell and enter =SORT(UNIQUE(FILTER(MasterTable[Requester],MasterTable[Status]="Authorized"))).
In the cell to the right, enter =COUNTIFS(MasterTable[Requester],A2,MasterTable[Status],"Authorized").
In the next column, enter =SUMIFS(MasterTable[Cost],MasterTable[Requester],A2,MasterTable[Status],"Authorized").
Select the COUNTIFS and SUMIFS formula cells, then drag the fill handle down beside the unique requester list.
Use a PivotTable to summarize authorized requests by requester
Use this method when you prefer a no-formula, interactive summary or need a quick built-in report.
Build a Dynamic Requester Summary in WPS Spreadsheets
WPS Spreadsheets can create the same Excel-compatible formula or PivotTable summary directly. Use UNIQUE, FILTER, COUNTIFS, and SUMIFS to automatically list unique authorized requesters, count their requests, and total costs.
- 1. Download and open your workbook: Install WPS Office, open WPS Spreadsheets, and load your master data workbook.
- 2. Create the summary sheet: Use the UNIQUE and FILTER formulas or Insert > PivotTable as described above.
- 3. Save in Excel format: Go to Menu > Save As and choose .xlsx to keep compatibility with Microsoft Excel.
Frequently Asked Questions
Do UNIQUE and FILTER formulas work in WPS Spreadsheets?
Yes, current versions of WPS Spreadsheets support UNIQUE and FILTER for dynamic arrays. If your version returns #NAME?, update WPS Office or use the PivotTable method instead.
How do I show only authorized requests in the summary?
In the formula method, include a condition like MasterTable[Status]="Authorized" inside FILTER, COUNTIFS, and SUMIFS. In a PivotTable, drag Status to Filters and select Authorized.
Can I build this summary without formulas?
Yes. Click any cell in the master data, go to Insert > PivotTable, place Requester in Rows, Status in Filters, and Cost in Values, then filter by Authorized.
Why is my formula summary not updating when I change the master data?
Make sure your formulas reference the table or full data range, not a fixed row range, and confirm calculation is automatic under Formulas > Calculation Options in WPS Spreadsheets.
Does WPS Spreadsheets keep this workbook compatible with Excel?
Yes. WPS Spreadsheets opens and saves .xlsx and .xls files, and the formulas and PivotTable remain usable in Microsoft Excel.
