In this article 6 sections
- 1. Check the range and criteria format first
- 2. Clean up leading, trailing, and nonprinting characters
- 3. Check wildcard symbols in the value you are searching for
- 4. Check whether values that look like numbers are text
- 5. Know the rules for letter case and long strings
- 6. Checklist when 0 is returned
When Excel COUNTIF returns 0, it is easy to conclude immediately that there is no data. However, even when cells appear to contain the same text, differences such as leading or trailing spaces, nonprinting characters, and wildcard symbols often prevent the criteria from matching. This article organizes what to check when 0 is returned, based on the COUNTIF rules in Microsoft support documentation.
1. Check the range and criteria format first
COUNTIF uses the syntax =COUNTIF(range, criteria). Microsoft’s examples enclose the criteria string in double quotation marks, as in =COUNTIF(A2:A5,"apples"), or reference a cell containing the criteria, as in =COUNTIF(A2:A5,A4). First check whether the range includes all the actual data and whether the criteria were entered incorrectly without quotation marks.
2. Clean up leading, trailing, and nonprinting characters
Microsoft explains that COUNTIF can return unexpected values when text contains leading or trailing spaces or nonprinting characters. The tools recommended in the documentation are the TRIM and CLEAN functions. For example, to clean values in column C in an adjacent column, enter =TRIM(C3), then paste the results as values for comparison. Do not delete the original column; keep it preserved.
TRIM is designed around regular spaces (ASCII 32). Data copied from web pages may contain nonbreaking spaces (code 160), which the documentation states TRIM does not remove. If the result remains 0 after using TRIM, check whether such characters are still present.
3. Check wildcard symbols in the value you are searching for
In COUNTIF criteria, * means any number of characters, and ? means one character. If the value you are searching for contains either symbol, it is interpreted as a pattern rather than as literal text. To search for the actual symbol, place a tilde (~) before it, as in ~* or ~?. The documentation also identifies this as a commonly missed point.
4. Check whether values that look like numbers are text
Even if a value appears as a number on screen, a value stored as text may be treated differently from a numeric criterion. Enter =ISTEXT(C3) in an empty cell; if it returns TRUE, that cell contains a text value. Check several samples, then apply the same standard to the entire column.
5. Know the rules for letter case and long strings
According to the documentation, COUNTIF is not case-sensitive. apples and APPLES are counted as the same value. In contrast, the documentation warns that criteria strings longer than 255 characters may produce incorrect results, so in such cases consider splitting them with the CONCATENATE function or the & operator.
6. Checklist when 0 is returned
- Check whether the range includes all the data.
- Check whether the criteria string is enclosed in quotation marks and use TRIM to check for leading or trailing spaces in the criteria cell.
- If the value you are searching for contains * or ?, add ~ to search for the literal symbol.
- Use ISTEXT to check samples for text values mixed into the column being compared with a numeric criterion.
- If you are referencing another workbook, keep that file open. The documentation explains that referencing a closed workbook can produce a #VALUE! error.
Related articles: How to check Excel XLOOKUP·VLOOKUP #N/A errors ↗, How to fix Excel SUMIFS #VALUE! errors ↗, How to fix Excel #DIV/0! errors ↗
Sources: Microsoft Support - Use the COUNTIF function in Microsoft Excel ↗, Microsoft Support - TRIM function ↗
Sources and original article
Read the Korean original on Naver ↗
This article reflects public information available when it was written. Specifications, prices and schedules can change; check the linked official documentation.
This English edition was written and translated with AI assistance, with sources provided. For errors or additional information, please request a correction. Editorial policy