Count Overdue Due Dates by Month and Year in WPS Spreadsheets
Question details
Count due dates in a specified month and year that are either completed after the due date or remain incomplete and past due.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user maintains a worksheet with due dates and completion dates, and needs a monthly overdue total for reporting or follow-up.
- Observed behavior
- The formula must count a row when the completion date is later than the due date, or when the completion date is blank and the due date is before today.
Confirm that the due date and completion date columns contain real date values rather than text, and note where the selected month and year are stored (for example, year in A122 and month in B122).
Use SUMPRODUCT to Count Overdue Due Dates by Month and Year
Best for a single formula that avoids Ctrl+Shift+Enter and works directly in WPS Spreadsheets.
SUMPRODUCT evaluates the date and completion conditions as arrays and returns the count without requiring legacy array entry.
Keep ranges bounded to avoid unnecessary calculation over whole columns.
Click the cell where you want the overdue count to appear, such as C122.
Type =SUMPRODUCT((J2:J27<>"")*(YEAR(J2:J27)=A122)*(MONTH(J2:J27)=B122)*((J2:J27<K2:K27)+((J2:J27<TODAY())*(K2:K27="")))) and press Enter.
Change J2:J27 and K2:K27 to match your actual due date and completion date columns, and update A122 and B122 to your month and year cells.
Use a Legacy SUM Array Formula for Compatibility
Use this when you need the exact SUM array formula from older Excel versions or prefer array entry.
Create the Overdue Due Date Count in WPS Spreadsheets
WPS Spreadsheets supports the same date, logic, and array functions as Excel, so you can enter this overdue-count formula directly in your workbook and get the monthly total without switching tools.
- 1. Open your workbook: Launch WPS Spreadsheets and open the .xlsx or .xls file that contains the due dates and completion dates.
- 2. Select the result cell: Click the cell where you want the monthly overdue total to appear.
- 3. Enter the formula: Type the SUMPRODUCT formula and press Enter, or use the SUM array formula and press Ctrl+Shift+Enter if prompted.
- 4. Verify the result: Change the month or year cell to confirm that the count updates for different periods.
Frequently Asked Questions
Why does the formula return 0 when my due dates look overdue?
Check that the date cells are actual dates and not text. Select a date cell and confirm its number format; text dates must be converted before YEAR, MONTH, and comparison operators can work correctly.
Should I use entire column references in this formula?
No. Use bounded ranges such as J2:J27 and K2:K27. Whole-column references force WPS Spreadsheets or Excel to evaluate more than one million cells and can slow the workbook.
Do I need Ctrl+Shift+Enter in WPS Spreadsheets?
If you use the SUMPRODUCT version, press Enter normally. If you use the SUM array formula and your version does not spill array results automatically, press Ctrl+Shift+Enter.
How do I count only blank completion dates that are overdue?
Use a formula that checks only blank completion dates and past due dates, for example =SUMPRODUCT((YEAR(J2:J27)=A122)*(MONTH(J2:J27)=B122)*(J2:J27<TODAY())*(K2:K27="")).
Will this workbook formula work in both WPS Spreadsheets and Microsoft Excel?
Yes, the functions and formula syntax are compatible with standard Excel, so the same bounded formula can be used in both applications.
