Function Problems

How to Fix Excel COUNTIF Returning Zero When Searching for a Word

Maira Mehtab Sep 22, 2026 869 views

Question details

The user is experiencing an issue where the COUNTIF formula returns a zero value despite the target word existing within the cell contents of the specified range.

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Attempting to count the number of cells that contain a specific word or substring among other text.
Observed behavior: The COUNTIF function fails to recognize the partial match and returns zero instead of the actual cell count.

Before you start

Check your target range to ensure the cells contain the exact spelling of the word you are searching for, and note whether the word appears by itself or alongside other text.

Solution 1Recommended

Use Wildcard Characters in the COUNTIF Formula

Add asterisk (*) wildcards directly to your search criteria so the formula knows to look for the word anywhere within the cell's text.

By default, the COUNTIF function looks for an exact match. If your target cell contains additional text besides the search word, COUNTIF will return zero. To solve this, you need to use an asterisk (*) as a wildcard, which represents any sequence of characters.

Select the result cell

Click on the empty cell where you want the final count result to appear.

Enter the COUNTIF formula

Type the COUNTIF formula and place asterisks around your target word inside the quotation marks. For example: =COUNTIF(A1:A10, "*word*").

Calculate the result

Press Enter to calculate. The formula will now successfully count all cells containing the specified word, regardless of any surrounding text.

Wildcard Application: You can also use "word*" to find cells starting with the word, or "*word" to find cells ending with the word.

Solution 2

Combine Wildcards with a Cell Reference

If your search term is stored dynamically in another cell, use the ampersand (&) operator to join wildcard characters with the cell reference.

WPS Spreadsheet Solution

Quickly Count Partial Text Matches in WPS Spreadsheet

WPS Spreadsheet fully supports wildcard usage in functions like COUNTIF, allowing you to accurately track and count text data without complex workarounds or compatibility issues.

1. Open your file in WPS: Launch WPS Office and open your spreadsheet document containing the data range to be counted.
2. Start the COUNTIF Function: Click on your target cell and type =COUNTIF( to bring up the intelligent formula prompt.
3. Apply criteria with wildcards: Select your data range, enter your criteria utilizing wildcards (e.g., "*keyword*"), and press Enter to instantly generate your exact count.

Fully compatible with Microsoft Excel formulas, including COUNTIF, SUMIF, and wildcards.Lightweight, fast, and completely free to use for daily data processing.Provides intelligent formula prompts and real-time syntax checking to prevent zero-value errors.

microsoft office alternative - wps office

Frequently Asked Questions

Why does COUNTIF return 0 without wildcards?

By default, COUNTIF requires an exact match. If a cell contains 'apple pie' and your formula searches for 'apple', it returns 0 because the cell contains additional text. Wildcards tell the formula to ignore the extra characters.

Are wildcards case-sensitive in the COUNTIF function?

No, the COUNTIF function is not case-sensitive. Searching for "*word*" will count 'Word', 'WORD', and 'word' equally.

What if I need to count a literal asterisk character?

If you want to count an actual asterisk (*) rather than using it as a wildcard, you must place a tilde (~) directly before it in your criteria, like this: "*~**".

Can I use COUNTIF to find cells that start or end with a specific word?

Yes. To count cells starting with a specific word, place the wildcard only at the end: "word*". To count cells ending with a word, place the wildcard at the beginning: "*word".

Excel & Data Problems