How to Count Text Occurrences by Year with COUNTIFS in WPS Office Spreadsheets
Question details
The user needs to count how many times a specific text phrase appears in a column when the corresponding date falls within a selected year, using COUNTIFS with date boundaries and wildcard matching.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a worksheet with date and text columns and wants a formula to count records where the date belongs to a target year and the text contains a specific phrase.
- Observed behavior
- The formula should return an accurate year-based text count and should handle text inside longer cell values.
Make sure your date column contains real date values rather than text, and put the target year in a cell such as E2 as a number. If you plan to use a table, confirm the data range has clear column headers.
Use COUNTIFS with Date Boundaries and Wildcard Matching
This is the most compatible method for WPS Office and Excel: it counts rows by date range and matches text anywhere in the cell.
Using DATE boundaries avoids regional date-format issues and does not require an array formula. The wildcard asterisks let COUNTIFS match a phrase even when the cell contains additional text.
Click the cell where you want the count to appear, such as F2.
Type =COUNTIFS(A2:A34,">="&DATE(E2,1,1),A2:A34,"<"&DATE(E2+1,1,1),B2:B34,"*General Operating Support*") and press Enter.
Change the year in E2 and confirm that the formula recalculates and returns the expected count.
Use Structured Table References for Automatic Range Expansion
Use this approach when the data range will grow over time; table references automatically include new rows.
Use FILTER and ROWS for a Dynamic Array Alternative
Use this method in newer WPS Office or Excel versions when you want a dynamic array formula based directly on YEAR comparison.
Count Text Occurrences by Year in WPS Office Spreadsheets
Open your workbook in WPS Office Spreadsheets and use the same COUNTIFS date-boundary formula to get accurate year-based text counts without switching tools.
- 1. Open your workbook in WPS Office: Launch WPS Office Spreadsheets and open the .xlsx workbook that contains your date and text columns.
- 2. Select the result cell: Click the cell where you want the count to appear, such as F2.
- 3. Enter the COUNTIFS formula: Type =COUNTIFS(A2:A34,">="&DATE(E2,1,1),A2:A34,"<"&DATE(E2+1,1,1),B2:B34,"*General Operating Support*") and press Enter.
- 4. Verify and adjust: Change the year in E2 or edit the text phrase in the formula to confirm the count recalculates correctly.
Frequently Asked Questions
Why should I use DATE boundaries instead of YEAR=2011 in COUNTIFS?
COUNTIFS criteria cannot process YEAR(A2:A34)=E2 directly as a range condition. Use ">="&DATE(E2,1,1) and "<"&DATE(E2+1,1,1) to include every date in the target year.
How can I count text that appears inside a longer cell value?
Wrap the search text with wildcard characters, for example "*General Operating Support*", so COUNTIFS counts cells that contain the phrase anywhere.
Can I use A:A as the range in this COUNTIFS formula?
Whole-column references can work but may slow down the workbook and pull in unwanted values. Use a bounded range such as A2:A34 or a structured table reference like Transactions[Payment Date].
Does this COUNTIFS formula work in WPS Office Spreadsheets?
Yes. WPS Office Spreadsheets supports COUNTIFS, DATE, and wildcard formulas with the same syntax as Excel, so you can open .xlsx workbooks and use the formula directly.
How do I make the formula update automatically when I add new rows?
Convert the range to a table with Ctrl+T or Insert > Table, then replace fixed ranges with structured references such as Transactions[Payment Date]. The table expands automatically and the formula continues to include new rows.
