Count Nonmatching Text Values in WPS Spreadsheets
Question details
Count nonblank spreadsheet entries that are not one of several predefined text categories, such as New Job, Retirement, or End of Contract.

- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user needs to calculate how many entries should be classified as Other after known category values have been counted.
- Observed behavior
- The desired result is a count of populated cells that do not match any of the specified category text values.
Confirm which column contains the category text and identify the first and last data rows. Exclude the header row and use a bounded range, such as F2:F1000, rather than a full-column reference when possible.
Subtract Existing Category Counts From the Total Nonblank Entries
Use this method when each predefined category has already been counted in separate cells.
This formula returns the number of populated entries that are not included in the existing category totals. It is useful when your category count formulas are already listed together.
In WPS Spreadsheets, verify that the counts for your predefined categories are in cells such as K24:K30.
Select the cell where you want the Other total to appear, type =COUNTA(F2:F1000)-SUM(K24:K30), and press Enter. Replace F2:F1000 with your actual category-data range.
Compare the result with the source values in column F to confirm that blank cells and all categories represented in K24:K30 are excluded.

Use COUNTIFS to Exclude Specific Text Categories Directly
Use this method when you want one formula that excludes named category values without relying on separate category-count cells.
Classify and Count Other Entries with WPS Spreadsheets
WPS Spreadsheets lets you use familiar COUNTIFS, COUNTA, and SUM formulas to count nonblank text entries outside your defined categories. Enter the formula in a results cell, adjust the source range and category names, and WPS calculates the Other total instantly.
- 1. Open the worksheet in WPS Spreadsheets: Open your .xlsx or .xls file, then locate the column containing the category values.
- 2. Choose an output cell: Click an empty cell beside your category summary or reporting table.
- 3. Enter a COUNTIFS formula: Type a formula such as =COUNTIFS(F2:F1000,"<>",F2:F1000,"<>New Job",F2:F1000,"<>Retirement",F2:F1000,"<>End of Contract").
- 4. Confirm the calculated Other total: Press Enter and review the result; update the range or category text if your source data changes.

Frequently Asked Questions
How do I count cells that are not equal to several text values?
Use COUNTIFS with one nonblank criterion and one not-equal criterion for every excluded value. For example: =COUNTIFS(F2:F1000,"<>",F2:F1000,"<>New Job",F2:F1000,"<>Retirement").
Why should I exclude blank cells in a COUNTIFS formula?
A blank cell does not equal category text such as New Job or Retirement, so it can be counted unintentionally. Add the criterion "<>" to the same range to count only populated cells.
Can I calculate Other by subtracting category totals?
Yes. If all known category counts are in K24:K30, use a formula such as =COUNTA(F2:F1000)-SUM(K24:K30). Ensure the COUNTA range excludes the header row.
Do the category names in COUNTIFS need to match exactly?
Yes. The criteria should use the same text as the entries in the worksheet, including spaces and punctuation. Edit each criterion if the source labels differ.
Can WPS Spreadsheets open Excel files that use COUNTIFS formulas?
Yes. WPS Spreadsheets supports common Excel workbook formats, including .xlsx and .xls, and can calculate COUNTIFS formulas in compatible worksheets.




