How to Count Members by Enrollment Date at Each Month-End in WPS Spreadsheets
Question details
Use an Excel dynamic-array formula in WPS Spreadsheets to count all members whose enrollment date is on or before each month-end in the current calendar year.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a list of enrollment dates and a list of month-end dates, and needs a cumulative total for each month-end.
- Observed behavior
- A formula-driven dynamic result is needed so monthly counts update automatically as dates are added or changed.
Confirm that the enrollment dates are real date values, not text, and define two named ranges: Dates for the enrollment date cells and Months for the month-end date cells.
Use BYROW and COUNTIFS to Count Enrollments by Month-End
Best for Excel 365 or WPS Spreadsheets with dynamic arrays; this single formula spills all cumulative monthly counts automatically.
BYROW passes each month-end date to COUNTIFS, which counts every date in Dates that is less than or equal to that month-end. This creates a cumulative count through the year.
Click the first cell where the month-end counts should appear, for example the cell next to the first month-end date in the Months range.
Type =BYROW(Months,LAMBDA(monthEnd,COUNTIFS(Dates,"<="&monthEnd))) and press Enter.
Select the spilled results, click Home > Number Format, and apply Number format. Keep the Months cells formatted as Date or use a custom format such as mmmm to display month names.
Use COUNTIFS with Direct Cell References for Wider Compatibility
Use this method if your WPS Spreadsheets or Excel build does not support LAMBDA or BYROW dynamic-array functions.
Create Dynamic Month-End Enrollment Counts in WPS Spreadsheets
WPS Spreadsheets supports Excel-compatible dynamic-array formulas, so you can open the workbook, apply the BYROW and COUNTIFS formula, and get automatic cumulative member counts by month-end without manual filtering or counting.
- 1. Open WPS Spreadsheets: Launch WPS Spreadsheets and open the enrollment workbook that contains the Dates and Months ranges.
- 2. Select the formula output cell: Click the first empty cell next to your Months list where the cumulative counts should spill.
- 3. Enter the formula: Type =BYROW(Months,LAMBDA(monthEnd,COUNTIFS(Dates,"<="&monthEnd))) and press Enter.
- 4. Format and verify: Apply Number format to the spilled counts and Date or month-name format to the Months column, then confirm that the totals update when the Dates range changes.
Frequently Asked Questions
Can I use this formula if my Dates and Months ranges have different lengths?
Yes. BYROW uses the Months range for the output, and COUNTIFS checks the entire Dates range. Just make sure both named ranges point to the actual data and that Dates contains valid date values.
Why does my BYROW formula return #SPILL?
A #SPILL error usually means the output area is not fully empty. Clear all cells below and to the right of the formula cell so the dynamic array has enough room to spill the monthly counts.
How do I define the named ranges Dates and Months in WPS Spreadsheets?
Select the enrollment date cells, click the Name Box to the left of the formula bar, type Dates, and press Enter. Repeat for the month-end date cells using the name Months.
How can I display only the month name instead of a full date?
Select the Months cells, right-click and choose Format Cells, go to Custom, and enter mmmm or mmm dd in the Type box, then click OK.
