COUNTIF Not Working? Fix Common Excel Counting Errors

If COUNTIF is not working properly in Excel, first check that its range and criterion refer to the intended cells, then look for mismatched text, extra spaces, numbers stored as text, or a criterion written in the wrong format. COUNTIF uses the pattern =COUNTIF(range, criteria); a small difference in either argument can make a result appear wrong even when the formula is valid.

How COUNTIF works

COUNTIF checks each cell in one range against one condition and returns the number of cells that meet it. The range can contain text, numbers, dates, or blanks, while the criterion can be a value, text, expression, or cell reference. The function is not case-sensitive: “North” and “north” match the same text criterion. By default, text criteria match a whole cell rather than any cell that merely contains those letters.

For example, suppose A2:A8 contains North, South, North, East, north, a blank cell, and Northeast. With that layout, the formula =COUNTIF(A2:A8,"North") returns 3. It counts North twice and north once, but does not count Northeast because that is not an exact whole-cell match.

COUNTIF is useful for a single condition, such as counting orders with a particular status. For conditions that must all be true, use COUNTIFS. For example, a sales report might count rows where the region is North and the status is Complete. COUNTIF itself does not combine two separate conditions into one test.

Check the formula syntax and cell references

Start by confirming the formula has two arguments: the cells to inspect and the condition to apply. A missing comma, misplaced parenthesis, or reference to the wrong column can cause an error or a plausible but incorrect count. In Excel for Microsoft 365 on Windows, click the formula cell and inspect the formula bar. You can also select the formula and choose Formulas > Evaluate Formula to follow how Excel interprets it.

Check that the range starts and ends where you expect. If new records have been added below the range, they will not be counted unless the formula includes them. Conversely, a range that is too large may include totals, notes, or unrelated data. Structured table references can expand automatically as table rows are added, which can help keep a count current.

If the criterion is stored in another cell, check that the reference points to the right place. For example, with the category values in A2:A20 and the requested category in E2, =COUNTIF(A2:A20,E2) counts occurrences of the value in E2. If you copy the formula down and want the source range to remain fixed, use absolute references, such as =COUNTIF($A$2:$A$20,E2). The first formula returns the number of E2 matches in the stated range; the second keeps that range fixed when copied while allowing the criterion reference to move.

Write criteria with operators correctly

Criteria that use comparison operators need special attention. Put the operator and value together inside quotation marks when the value is typed into the formula. If the value is in a cell, join the operator to the cell reference with the ampersand (&). Without that join, Excel may treat the operator as ordinary text rather than a comparison instruction.

Suppose B2:B6 contains the numbers 4, 7, 10, 12, and 15, and D1 contains 10. With this layout, =COUNTIF(B2:B6,">"&D1) returns 2, because only 12 and 15 are greater than 10. To include values equal to the threshold, change the operator to >=. The operators commonly used in criteria are =, >, <, >=, <=, and <> for “not equal to.”

When a criterion looks right but counts nothing, temporarily place the condition in a visible cell or simplify it. Check that a cell-based threshold is numeric if the data being compared are numbers. A text value such as “10 units” is not the same as the number 10 and will not behave as a plain numeric threshold.

Look for hidden spaces and inconsistent text

Text that looks identical on screen may contain leading spaces, trailing spaces, nonprinting characters, or a different apostrophe or dash. A cell containing “North ” with a space at the end may not match the criterion “North.” Such problems are common after copying data from websites, exported reports, or other systems. Widening the column or clicking a cell can reveal some differences, but not all invisible characters.

For a quick check, compare the apparent value with a freshly typed criterion and inspect the source cells. Excel’s Find and Replace can remove a known unwanted character, but be careful: replacing spaces throughout a range can also damage legitimate multiword names. For repeated cleanup, use a helper column and the TRIM function to remove ordinary leading and trailing spaces and reduce repeated spaces between words. TRIM does not remove every special whitespace character that can arrive from web data, so imported text may need further cleaning.

Also check for inconsistent punctuation and spelling. “Pending,” “Pending order,” and “Pending-order” are different values for an exact match. A dropdown list or data validation can prevent future spelling variations, but it does not automatically repair differences already present in the data.

Check numbers and dates stored as text

Imported values can look numeric while being stored as text. This may happen when data include leading apostrophes, spaces, or formatting from another program. Text and numbers can behave differently in comparisons, especially when a criterion uses greater-than or less-than. Check the cells’ alignment and warning indicators, but do not rely on appearance alone. A reliable fix is to convert the source values to genuine numbers, then test the count again.

Dates are numbers in Excel, displayed using a date format. A date typed into a formula as text can be interpreted according to regional date settings, so it is safer to build date criteria with DATE or refer to a cell containing a real date. With actual dates in C2:C31, =COUNTIF(C2:C31,">="&DATE(2026,1,1)) counts dates on or after January 1, 2026. The result depends on the dates in that range; DATE creates the comparison date without relying on how a typed date string is interpreted.

If the cells contain both dates and times, an exact date criterion may not count every timestamp on that day, because a timestamp includes a fractional time component. To count records from one day, use two conditions: greater than or equal to the day’s start, and less than the next day. With timestamps in C2:C31, =COUNTIFS(C2:C31,">="&DATE(2026,1,1),C2:C31,"<"&DATE(2026,1,2)) counts timestamps on January 1, 2026, including any time during that day.

Use wildcards for partial text matches

If you want to count cells that contain a word as part of a longer entry, use wildcard characters. The asterisk (*) stands for any number of characters, including none; the question mark (?) stands for exactly one character. Wildcards work with text criteria, and matching remains case-insensitive.

Suppose A2:A6 contains East, Northeast, Southeast, West, and Eastside. With that layout, =COUNTIF(A2:A6,"*east*") returns 4, counting every entry that contains “east.” To find a three-character code beginning with A, a criterion such as “A??” matches A followed by exactly two characters. Be aware that a broad wildcard may include more values than intended: “*east*” also matches “Southeast,” not just the standalone region East.

To search for a literal asterisk or question mark in text, precede the wildcard with a tilde (~). For example, the criterion "~*" means a literal asterisk rather than “any sequence of characters.” This escape rule is useful when counting product codes or notes that contain wildcard symbols.

Check calculation settings and filtered rows

Sometimes the formula is correct but the displayed result has not updated. In Excel for Microsoft 365 on Windows, check Formulas > Calculation Options and select Automatic if the workbook is set to Manual. Press F9 to recalculate formulas; on many Mac keyboards, use Fn+F9 if the function keys control hardware features. Recalculation can matter after changing source data or opening a workbook that was saved with manual calculation.

COUNTIF counts matching cells whether their rows are visible or hidden, including rows hidden by a filter. Filtering a list does not change the COUNTIF result to count only visible records. This is a frequent source of confusion when someone compares a formula with the rows currently shown on screen. If the requirement is specifically to count visible matching rows, a standard COUNTIF is not enough by itself; use a carefully designed helper column or another approach suited to the workbook’s structure.

Likewise, formatting a cell to look blank does not always make it empty. A formula returning an empty string, a space, and a truly empty cell are distinct situations. Determine whether the cells are genuinely empty or contain formulas or spaces before choosing a blank-counting criterion.

Choose the right counting function

Use COUNTIF for one condition and COUNTIFS when every condition must be met across corresponding ranges. For instance, to count rows where a status is either Open or Pending, add two separate COUNTIF results; with statuses in A2:A10, =COUNTIF(A2:A10,"Open")+COUNTIF(A2:A10,"Pending") returns the combined number of Open and Pending entries. If the list contains three Open entries and two Pending entries, the result is 5.

Other functions solve different questions. COUNTA counts nonempty cells, regardless of whether their contents match a condition. COUNT counts numeric values. COUNTBLANK counts blank cells, though formulas returning an empty string can make blank-counting behavior less intuitive. Choose based on what you mean by “count”: matching a rule, counting numbers, or finding cells that are not empty are separate tasks.

A practical troubleshooting order

Work through the checks in a consistent order instead of rewriting the formula repeatedly. This makes it easier to identify whether the issue is the formula, the source data, or workbook calculation.

Click the result cell and confirm the range and criterion in the formula bar.
Test the criterion against a small, known subset of the data and verify the expected matches manually.
Check for spaces, spelling differences, hidden characters, or inconsistent number and date types.
Decide whether you need an exact match, a comparison, or a wildcard search.
Confirm calculation is set to Automatic and remember that filters do not limit COUNTIF to visible rows.
If multiple conditions are required, replace COUNTIF with COUNTIFS or combine separate counts when the logic is “this or that.”

For example, if a count of “East” is too low, first test an exact criterion against a handful of rows. If entries include “East Region,” use a wildcard only if those longer labels should count. If the count is still unexpectedly low, inspect the source for trailing spaces or inconsistent spelling. This separates a legitimate difference in matching rules from a broken formula.

Frequently Asked Questions

Why does COUNTIF return zero when the text looks identical?
Check for extra spaces, nonprinting characters, punctuation differences, or a criterion that does not match the cell’s full text. COUNTIF ignores capitalization, so uppercase versus lowercase alone is not the cause.

Does COUNTIF distinguish uppercase and lowercase?
No. COUNTIF is not case-sensitive. If the task requires case-sensitive counting, COUNTIF is not the appropriate function on its own; a different formula approach is needed.

Why does COUNTIF count rows hidden by a filter?
COUNTIF examines all cells in its specified range, including filtered-out and manually hidden rows. Filtering changes what is displayed, not which cells COUNTIF evaluates.

Can COUNTIF match part of a cell?
Yes. Use an asterisk before and/or after the text to match a substring, such as “*east*” for cells containing east anywhere. That can also match longer words or labels, so check whether those should be included.

What is the difference between COUNTIF and COUNTIFS?
COUNTIF applies one condition. COUNTIFS applies multiple conditions, and a row is counted only when it satisfies all the specified conditions. For an “either this or that” test, separate COUNTIF results can be added.

Why does my COUNTIF result not change after editing data?
Check Formulas > Calculation Options > Automatic. If calculation is set to Manual, press F9 to recalculate, or change the workbook setting back to Automatic.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.