If COUNTIF is returning the wrong number, first check that the range and criterion refer to the cells and values you intend; then look for spaces, numbers stored as text, dates, or wildcard characters that change what Excel matches. COUNTIF is not case-sensitive, and it counts matching cells even when they are hidden by a filter.
Check the range and criterion first
COUNTIF counts cells in one range that meet one condition. A wrong starting row, an accidentally shortened range, or a criterion that points to the wrong cell can make a correct formula look broken. Check both arguments before changing the data.
With statuses in B2:B20, for example, suppose eight cells contain the status Paid. The formula =COUNTIF(B2:B20,"Paid") returns 8. If the formula instead refers to B2:B19, it omits the last row; if it refers to C2:C20, it checks a different column. Select the formula cell and inspect the colored range outline Excel displays while you edit the formula.
Also confirm whether the criterion is meant to be an exact value or a pattern. COUNTIF with the criterion Paid matches the value Paid, not a cell containing Paid in full. To find text that contains a word, a wildcard criterion is needed. If the criterion comes from another cell, confirm that cell contains the intended value and has not been overwritten or left blank.
Understand how COUNTIF matches values
COUNTIF treats text matching without regard to letter case: Paid and PAID match the same text criterion. By default, text criteria match the whole cell value, though wildcard characters can broaden the match. For numbers, a criterion can be a value such as 10 or a comparison such as greater than 10. When a comparison operator is part of the criterion, put it in quotation marks and join it to a cell reference when necessary.
With scores in A2:A10 and a limit of 10 in D1, the formula =COUNTIF(A2:A10,"<="&D1) returns 4 if exactly four scores are 10 or lower. The ampersand joins the comparison text to the value in D1; typing the criterion as "<=D1" would search for text beginning with those characters rather than use D1's value.
COUNTIF accepts one condition. If a count depends on two or more conditions, use COUNTIFS, which applies a condition to each corresponding range. For instance, counting rows where a department is Sales and a status is Paid is a multi-condition task, not a reason to combine unrelated tests into one COUNTIF criterion.
Find hidden spaces and nonprinting characters
Text that looks identical on screen may not be identical to Excel. A trailing space in "Paid " or a nonbreaking space copied from a webpage can prevent an exact text match. Extra spaces at the beginning of a cell cause the same problem. Widening the column, inspecting the formula bar, or comparing the source with the criterion can reveal ordinary spaces, but unusual characters may be difficult to see.
If source data should be cleaned, use a helper column rather than altering the original data without a backup. TRIM removes ordinary leading and trailing spaces and reduces repeated internal spaces to one; it does not remove every kind of nonprinting or nonbreaking character. CLEAN removes certain nonprinting characters, but it does not remove all Unicode characters either. For imported data, the appropriate cleanup depends on where the text came from.
A contains search can be useful when the cell has extra words or spaces. With descriptions in A2:A20 and the word Ann in D2, =COUNTIF(A2:A20,"*"&D2&"*") returns 2 if two descriptions contain Ann anywhere. This is a broader search than an exact match and is case-insensitive. It can also match unintended text, such as a longer word containing those letters, so use it only when partial matching is appropriate.
Check wildcard characters in criteria
The asterisk (*) means any number of characters, and the question mark (?) means any single character. That behavior is useful for pattern matching but surprising when the symbol is part of the actual data. The tilde (~) escapes a wildcard so Excel searches for it literally.
With entries in A2:A10 and one cell containing a literal asterisk, =COUNTIF(A2:A10,"~*") returns 1. Without the tilde, the asterisk criterion matches text strings broadly, so the result could be much larger. To search for a literal question mark, use ~? in the criterion; to search for a literal tilde, use ~~.
Remember that a cell reference used inside a wildcard criterion can itself contain * or ?. Those characters still act as wildcards. If the search term comes from user-entered data and must be treated literally, the criterion needs to escape any wildcard characters in that input.
Investigate numbers, dates, and blanks
Imported values can look numeric while actually being stored as text. A value such as 007 may be intentionally text because its leading zeros matter, whereas 7 may be a number. Number formatting changes how a genuine number appears, but it does not by itself convert text to a number. Check suspicious cells with the formula bar or Excel's error indicators, and use a consistent data type when the values represent the same kind of information.
Dates are stored as serial numbers in Excel, even though cells display calendar dates. Text that resembles a date may not behave like a real date in comparisons. When counting dates on or after January 1, 2026, with real Excel dates in A2:A20, =COUNTIF(A2:A20,">="&DATE(2026,1,1)) returns 3 if exactly three dates fall on or after that day. The DATE function avoids ambiguity from typing a date string whose interpretation can depend on regional settings.
Blank criteria need care. COUNTIF with an empty-string criterion can count genuinely empty cells and cells whose formulas return an empty string. Those two kinds of cells may look alike but are not the same: one has no content, while the other contains a formula. If the distinction matters, inspect the cells or test for formulas separately rather than assuming a displayed blank is an empty cell.
Know what filters and formatting do
COUNTIF counts matching cells in its range whether they are visible or hidden. Applying a filter does not change its result. This is a frequent source of confusion when a worksheet displays only a few matching rows but the count includes matching rows hidden by the filter. Hiding rows manually also does not make COUNTIF ignore them.
Number formats, font color, fill color, and conditional formatting generally affect appearance, not the underlying value COUNTIF evaluates. For example, a date formatted to display only its month still contains a date value; a number displayed with a currency symbol is still numeric if it was entered as a number. If a count is meant to depend on color or visibility, COUNTIF alone is not the right tool because it evaluates cell values and criteria, not the displayed appearance or filter state.
Similarly, a displayed value may be rounded while the stored value has more decimal places. A cell displaying 10.0 might contain 9.96 or 10.04, so a criterion of 10 does not necessarily match it as an exact value. Check the formula bar or increase displayed decimal places before deciding the count is wrong.
Use a systematic troubleshooting sequence
Changing several things at once makes it harder to identify the cause. Work from the formula outward and test the data with a small, known example. In Excel for Microsoft 365 on Windows, select the formula cell and use the formula bar to inspect its references; you can also select a range in the formula and press F9 to evaluate that selected expression while editing. Press Esc afterward to avoid replacing the formula with the evaluated value.
- Confirm the range includes every intended row and excludes headers or unrelated cells.
- Check the exact criterion, including comparison operators, quotation marks, and any referenced cell.
- Test whether the issue is exact versus partial matching, or whether * and ? are being treated as wildcards.
- Inspect suspicious text for leading, trailing, or unusual spaces, and check whether numbers and dates are stored in consistent types.
- Temporarily remove filters or compare with a small range whose matching cells you can count manually.
- If there are multiple conditions, use COUNTIFS; if the intended count depends on case, visibility, or formatting, choose an approach designed for that requirement.
For large worksheets, avoid copying a formula with a range that stops before newly added data. A structured Excel Table can help keep references aligned as rows are added. To make a table, select the data and choose Insert > Table, then ensure the header option is correct. Structured references are also easier to read, but verify that the formula is counting the intended table column.
Choose the right alternative when needed
COUNTIF is suitable for one value-based condition. COUNTIFS is the direct alternative for several conditions, such as a matching region and a date window. Its criteria ranges should correspond row by row; ranges of different sizes can produce an error or an unintended calculation. For case-sensitive text matching, COUNTIF is not enough because it ignores case. With text values in A2:A10 and the exact case-sensitive search term in D1, =SUMPRODUCT(--EXACT(A2:A10,D1)) returns 1 if exactly one cell matches D1 with identical capitalization.
If the goal is to count only visible rows after filtering, COUNTIF will not do that by itself. A helper column or a formula specifically designed to combine row visibility with a condition may be needed, and the right choice depends on the worksheet layout. If the goal is to count by color, consider counting the underlying category or status instead; relying on manually applied color can make a workbook harder to maintain because color is not a standard COUNTIF criterion.
For a quick cross-check, use Excel's Find feature on a small sample or sort the relevant column and inspect the distinct values. A second method can help establish whether the data or the formula is at fault, but it does not replace verifying the intended definition of a match. Decide whether leading zeros, extra words, hidden rows, and capitalization are meaningful before choosing a formula.
Frequently Asked Questions
Why does COUNTIF return zero when the text looks identical?
Check for leading or trailing spaces, nonbreaking spaces, and a criterion that points to the wrong cell. Also verify whether the formula is looking for an exact match when the cell contains additional words.
Does COUNTIF count filtered-out or hidden rows?
Yes. COUNTIF evaluates the values in its range without excluding rows hidden by a filter or manually hidden. Its result can therefore be larger than the number of matching rows currently visible.
Is COUNTIF case-sensitive?
No. COUNTIF treats uppercase and lowercase text as equivalent. Use a case-sensitive method such as EXACT within SUMPRODUCT if capitalization must match.
Why does COUNTIF count a blank-looking cell?
The cell may contain a formula that returns an empty string, or the criterion may be matching a value you did not expect. A formula-generated blank is not the same as a truly empty cell, so inspect the cell contents.
How do I count a word inside a longer cell value?
Use a criterion with asterisks before and after the search text to allow characters on either side. Remember that this partial match is case-insensitive and may also match the text inside a longer word.
Can COUNTIF count cells by color?
Not by itself. COUNTIF evaluates values, not fill or font color. A more maintainable design is to count a category or status stored in the cell data rather than relying on formatting.
Related
- Excel Conditional Formatting: Find Partial Text Matches
- Split a Cell in Excel: Separate Text or Unmerge Cells
- How to Merge Cells in Jupyter Notebook: A Practical Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Hanging Indent in Word: Set It Up and Fix Common IssuesTo give text a hanging indent in Microsoft Word, select the paragraph or paragraphs, open the Paragraph settings…
- Excel Not Letting Me Drag Formulas? Fix the Fill HandleIf Excel will not let you drag a formula, first check that the fill handle is enabled: go…
- Outlook Change View Not Working? Fixes That HelpIf Outlook’s Change View command is not working, first check that you are using classic Outlook for Windows:…
- Circular Reference Error in Excel: Find and Fix ItA circular reference error in Excel means a formula depends on its own result, either directly or through…
- Excel Link Not Responding? Fix Hyperlinks and LinksIf an Excel link is not responding, first check whether it is a clickable hyperlink or an external-workbook…
- Excel #NAME? Error: Causes and FixesYou are getting Excel’s #NAME? error because Excel cannot recognize some text in a formula as a valid…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.