Function Problems

Count Nonmatching Text Values in WPS Spreadsheets

Kushani NimanthikaKushani Nimanthika Nov 12, 2026 868 views

Question details

Count nonblank spreadsheet entries that are not one of several predefined text categories, such as New Job, Retirement, or End of Contract.

Count Nonmatching Text Values in WPS Spreadsheets
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.
Before you start

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.

Solution 1Recommended

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.

1
Check the category count cells

In WPS Spreadsheets, verify that the counts for your predefined categories are in cells such as K24:K30.

2
Enter the Other count formula

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.

3
Review the total

Compare the result with the source values in column F to confirm that blank cells and all categories represented in K24:K30 are excluded.

Subtract Existing Category Counts From the Total Nonblank Entries
Formula result: The result is the count of nonblank entries in the selected range that are not already included in the summed category counts.
WPS Spreadsheets formula tools

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. 1. Open the worksheet in WPS Spreadsheets: Open your .xlsx or .xls file, then locate the column containing the category values.
  2. 2. Choose an output cell: Click an empty cell beside your category summary or reporting table.
  3. 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. 4. Confirm the calculated Other total: Press Enter and review the result; update the range or category text if your source data changes.
Use COUNTIFS to exclude multiple text categories in one calculation.Apply COUNTA and SUM to derive an Other count from existing category totals.Open and edit Microsoft Excel .xlsx and .xls workbooks with formula compatibility.Use a familiar spreadsheet interface for quick formula editing and analysis.
microsoft office alternative - wps office

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.