Use WPS Office Spreadsheets COUNTIFS When Criteria Begins with a Less-Than Symbol
Question details
The user needs to count cells in WPS Office Spreadsheets when the criteria text starts with a less-than symbol, such as '<div>', and wants the symbol treated as literal text rather than a comparison operator.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Counting exact text matches in a column containing HTML-like text or values that begin with '<'.
- Observed behavior
- COUNTIF and COUNTIFS interpret the leading '<' as a comparison operator, causing incorrect counts or unintended criteria behavior.
Confirm your data range and that the leading '<' cells are stored as text. If you are using a large range, first test the formula on a small explicit range such as A1:A100.
Count literal less-than text with SUMPRODUCT exact comparison
Use this when you need a formula-only fix that compares cell contents literally and avoids COUNTIF criteria parsing.
COUNTIF and COUNTIFS evaluate criteria strings that begin with '<' as comparison operators. SUMPRODUCT performs an exact cell-by-cell equality test instead, so the less-than sign is not parsed as an operator.
Click the cell where you want the count result to appear in your WPS Office Spreadsheets worksheet.
Type =SUMPRODUCT(--(A$1:A$11=A1)) in the formula bar, replacing A$1:A$11 with your full data range and A1 with the first cell containing the literal '<' text you want to count.
Press Enter. If you see #NAME? after extending the range, use an explicit range such as A$1:A$10000 instead of a whole-column or table reference, and check for accidental named-range conflicts.
Add a helper column that quotes the less-than text before using COUNTIF
Use this if you prefer keeping COUNTIF or COUNTIFS and need a robust workaround for very large datasets.
Count Text That Starts with '<' in WPS Office Spreadsheets
Use SUMPRODUCT or a quoted helper column in WPS Office Spreadsheets to count cells beginning with a less-than symbol literally, so COUNTIF and COUNTIFS no longer treat the symbol as a comparison operator.
- 1. Open the workbook: Launch WPS Office and open the .xlsx workbook that contains the cells starting with '<'.
- 2. Select the result cell: Click the cell where you want the literal less-than text count to appear.
- 3. Enter the SUMPRODUCT formula: Type =SUMPRODUCT(--(A$1:A$10000=A1)) in the formula bar and press Enter.
- 4. Adjust the range: Replace A$1:A$10000 with the exact range of your data and A1 with the first criterion cell containing the less-than text.
Frequently Asked Questions
Why does COUNTIF treat text beginning with '<' as an operator?
COUNTIF criteria use comparison operators such as <, >, <=, and >=. When a criterion begins with '<', WPS Office Spreadsheets interprets it as a comparison rather than literal text. Use SUMPRODUCT(--(range=cell)) or a quoted helper column to force exact matching.
Can I use COUNTIFS with multiple criteria when one criterion starts with '<'?
Yes, but each text criterion beginning with '<' may be interpreted as an operator. Use SUMPRODUCT with multiple boolean terms, for example =SUMPRODUCT(--(A1:A100=A1),--(B1:B100=B1)), or apply the helper column method to each text column.
Why do I get #NAME? after extending SUMPRODUCT to more rows?
This can happen with whole-column references, certain table structured references, or accidental named-range conflicts. Use an explicit range such as A$1:A$10000 and make sure the formula uses only valid function names and column letters.
Will the helper column method work in Microsoft Excel too?
Yes. The =CONCAT(CHAR(34),A1,CHAR(34)) helper column and COUNTIF approach use standard formulas and are compatible with Microsoft Excel .xlsx files, so the same workbook can be shared between Excel and WPS Office Spreadsheets.
