How to Count Dates Before Today with a Mandatory Status in WPS Spreadsheets
Question details
The user needs a WPS Spreadsheets formula that counts dates in column Z that are earlier than today only when the matching cell in column L contains the value Mandatory.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is building a status-aware date tracking workbook and wants to total time-sensitive mandatory items based on a status column and a date column.
- Observed behavior
- The goal state is a dynamic count that updates with the current date; the formula returns the correct number only when date cells are real date values rather than text.
Confirm that column Z contains real date values instead of text that looks like dates. Also check that the Mandatory values in column L do not have leading or trailing spaces.
Use COUNTIFS to Count Mandatory Dates Before Today
The recommended method because COUNTIFS handles multiple criteria without array entry and is supported in WPS Spreadsheets.
COUNTIFS evaluates each row only when both criteria are true: the status cell equals Mandatory and the date cell is less than today's date.
Click the cell in your WPS Spreadsheets workbook where you want the count to appear.
Type =COUNTIFS(Database!L6:L200,"Mandatory",Database!Z6:Z200,"<"&TODAY()). Adjust the sheet name and row range to match your workbook.
Press Enter to calculate the result. The cell displays the number of rows that satisfy both conditions.
Use SUMPRODUCT as an Alternative Counting Formula
Use this if you prefer a formula that works without COUNTIFS or need to evaluate array-like conditions in older spreadsheet versions.
Count Mandatory Dates Before Today Directly in WPS Spreadsheets
Use COUNTIFS with TODAY() in WPS Spreadsheets to get a live count of rows where column Z is before today and column L is Mandatory. WPS Spreadsheets handles this formula natively and can open and save Microsoft Excel .xlsx workbooks without changing your data.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Spreadsheets and open the Excel file containing the Database sheet.
- 2. Select the formula cell: Click the cell where you want the mandatory-date count to appear.
- 3. Enter the COUNTIFS formula: Type =COUNTIFS(Database!L6:L200,"Mandatory",Database!Z6:Z200,"<"&TODAY()) and adjust the ranges as needed.
- 4. Press Enter to calculate: Press Enter to return the count; WPS updates the result automatically when today's date changes.
Frequently Asked Questions
Why does COUNTIFS return 0 when I can see dates before today?
Usually the date cells are stored as text. In WPS Spreadsheets, select the date column, open the Format Cells dialog, choose a date format, or use Text to Columns to convert text dates into real date values, then recalculate the formula.
Can I use full-column references like Database!L:L and Database!Z:Z in this formula?
Yes, WPS Spreadsheets accepts full-column references, but it may calculate more slowly in very large workbooks. For better performance, limit the range to the actual data area, such as L6:L200 and Z6:Z200.
How do I count dates between two dates when column L is Mandatory?
Use COUNTIFS with two date criteria: =COUNTIFS(Database!L6:L200,"Mandatory",Database!Z6:Z200,">="&start_date,Database!Z6:Z200,"<="&end_date). Replace start_date and end_date with cell references or DATE() values.
Does this formula update automatically when the date changes?
Yes, TODAY() is a volatile function in WPS Spreadsheets. The formula recalculates when the workbook recalculates, so it always compares against the current system date.
Can I use this formula in WPS Spreadsheets on mobile?
WPS Spreadsheets mobile supports COUNTIFS and TODAY(); the formula syntax is the same, but editing long ranges may be easier with a keyboard or desktop app.
