Count Unique Customers by Month in a Cumulative Range in WPS Office Spreadsheets
Question details
The user needs to calculate monthly counts of unique customers or unique customer-package pairs by package type from start and end dates, and formulas using UNIQUE and VSTACK have not returned the expected cumulative results.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A table contains customer numbers, package types, start dates, and end dates, and the user wants a cumulative monthly breakdown of unique customers by package.
- Observed behavior
- UNIQUE and VSTACK formulas either do not spill correctly or do not produce the required cumulative monthly grouping by package type.
Format your data as an Excel table with columns for Customer Number, Package, Start Date, and End Date, and verify that all date columns contain real date values rather than text. If the table name is not Table1, update the formula references to match the actual table name.
Create a Spill Report with LET, UNIQUE, TAKE, COUNTIFS, HSTACK, and VSTACK
Use this when you need a formula-driven, automatically updating report that shows each unique customer-package pair and monthly counts from start dates.
The formula builds a distinct list of customer-package pairs, calculates monthly date boundaries from the Start Date column, and uses COUNTIFS to count matching rows for each pair in each month.
Click an empty cell in a new worksheet and enter the following formula: =LET(distinctPairing,UNIQUE(Table1[[Customer Number]:[Package]]),customer,TAKE(distinctPairing,,1),package,TAKE(distinctPairing,,-1),monthStart,TRANSPOSE(UNIQUE(1+EOMONTH(+Table1[Start Date],-1))),monthEnd,EOMONTH(monthStart,0),counts,COUNTIFS(Table1[Customer Number],customer,Table1[Package],package,Table1[Start Date],">="&monthStart,Table1[Start Date],"<="&monthEnd),VSTACK(HSTACK({"Customer","Package"},TEXT(monthStart,"mmm yy")),HSTACK(distinctPairing,counts)))
Press Enter and verify that the first two columns list unique customer-package pairs and the following columns display counts for each month.
Summarize Monthly Unique Counts with a PivotTable and Distinct Count
Use this when you prefer an interactive summary and your WPS Office version provides Distinct Count in PivotTables, or after adding a helper column to flag qualifying rows.
Count Unique Customers by Month with WPS Office Spreadsheets
Use WPS Office Spreadsheets to open your existing Excel workbook, apply an Excel-compatible dynamic array formula, and generate a monthly customer-package count report. WPS Office preserves table references and .xlsx formatting, so you can continue working with familiar formulas without switching tools.
- 1. Open the workbook: Launch WPS Office Spreadsheets, click Open, and select the Excel workbook that contains your customer, package, start date, and end date data.
- 2. Format the source range as a table: Select the data range, go to Insert > Table, confirm the range, and rename the table to Table1 so the formula references match your workbook.
- 3. Enter the monthly count formula: Click a blank cell and enter the LET formula using UNIQUE, TAKE, EOMONTH, COUNTIFS, HSTACK, and VSTACK to create the customer-package monthly count matrix.
- 4. Review the spilled results: Press Enter and confirm that customer-package pairs appear in the left columns and each month's counts appear in the adjacent columns.
Frequently Asked Questions
Why does my UNIQUE/VSTACK formula return #NAME? in WPS Office?
The #NAME? error usually means your WPS Office version does not support one of the dynamic array functions such as VSTACK or HSTACK. Update WPS Office to the latest version, or use a PivotTable with Distinct Count if available, or add a helper column to flag unique package-month rows.
Can I open an Excel workbook with this data in WPS Office and keep the formulas?
Yes. WPS Office Spreadsheets opens and saves .xlsx files and supports Excel table references and many dynamic array functions. Open the file, review the table name, and enter the formula in a blank cell.
How do I group pivot table dates by month in WPS Office?
Create a PivotTable, drag the Start Date field to Columns, right-click any date cell in the PivotTable, choose Group, select Months, and click OK.
What does the formula actually count: distinct customers or appearances?
The formula returns one row for each unique customer-package pair and counts matching Start Date rows per month. If a customer appears multiple times in the same month for the same package, that cell can be greater than 1; use Distinct Count or a first-occurrence helper column for a strictly unique customer count.
Do I need to use Table1 as the table name?
No. If your table has a different name, either rename it to Table1, or replace every Table1 reference in the formula with the actual table name shown on the Table Tools Design tab.
