COUNTIF Does Not Work? Fix Common Excel Problems

COUNTIF usually fails because the value in its range does not match the criterion exactly, the criterion is written incorrectly, or the formula has not recalculated. Check the range and criterion first, then look for extra spaces, numbers stored as text, wildcard characters, or calculation settings; the fixes below address the most common causes in Excel for Microsoft 365 on Windows.

How COUNTIF works

COUNTIF counts cells in one range that meet one condition. Its syntax is =COUNTIF(range, criteria): the first argument is the cells to inspect, and the second is the condition. The condition can be a number, text, comparison, wildcard pattern, or a reference to a cell containing a criterion. COUNTIF returns a number, including zero when it finds no matches.

For example, with product names in A2:A6 containing Apple, Pear, Apple, Plum, and Pear, the formula =COUNTIF(A2:A6,"Apple") returns 2. COUNTIF does not distinguish uppercase from lowercase, so Apple and apple are both counted as matches. It does, however, require text to match the criterion apart from case: a cell containing Apple followed by a space is not the same as Apple.

COUNTIF accepts only one criterion. To count records that must meet multiple conditions, use COUNTIFS, which has a range-and-criterion pair for each condition. For example, it can count rows where a department is Sales and a status is Open. COUNTIF is not broken when it cannot express that request; it is simply the one-condition function.

Start with the range and criterion

Before changing the data, confirm that the formula points to the intended cells and that the criterion is what you mean to count. A range that begins one row too low, excludes newly added records, or includes a header can produce a plausible but wrong total. Click the formula cell and inspect the colored range outline, or select the formula and review its arguments in the formula bar.

With values 12, 18, 12, 7, and 18 in A2:A6, the formula =COUNTIF(A2:A6,12) returns 2. If the data actually starts in A1, the range A2:A6 silently omits the first value. If you expect the result to update when new rows are added, consider converting the data to an Excel table: select the data and choose Insert > Table. Table references can expand as rows are added, unlike a fixed range such as A2:A100.

Also check whether the formula was copied from another row. A relative reference such as A2:A20 shifts when copied; an absolute reference such as $A$2:$A$20 stays fixed. Add dollar signs when the range must remain the same as you fill the formula. Do not lock it automatically, though: a changing range may be intentional in a row-by-row summary.

Write criteria with comparison operators correctly

For a condition such as greater than 10, Excel needs the operator and value together as the criteria. Comparison operators include >, <, >=, <=, <> (not equal to), and =. When the value is typed directly, put the whole condition in quotation marks. With the values 4, 12, 15, 9, and 20 in B2:B6, =COUNTIF(B2:B6,">10") returns 3.

If the threshold is in a cell, join the operator to the cell reference with &. With those same values in B2:B6 and a threshold of 10 in D2, =COUNTIF(B2:B6,">"&D2) returns 3. Typing >D2 as a quoted string would ask Excel to find text matching those characters, not to compare values against D2.

Text criteria need quotation marks when typed directly into a formula, as in =COUNTIF(A2:A20,"North"). A criterion that refers to a cell is written as a reference, without wrapping the reference itself in quotation marks. If a formula displays as text instead of calculating, check for a leading apostrophe, a leading space before the equals sign, or the cell format set to Text. Change the format to General, then edit the formula and press Enter; changing the format alone may not convert an existing text entry.

Check spaces and hidden characters

One of the most common reasons a text count is too low is an invisible difference in the source data. Extra spaces at the beginning or end, nonbreaking spaces copied from a web page, and other imported characters can make entries look identical while Excel treats them as different strings. COUNTIF ignores letter case, but it does not automatically remove these characters.

To diagnose a suspected space, compare the length of a cell with the expected text. With a suspect entry in A2, =LEN(A2) returns the number of characters, including ordinary spaces. If the word should have five characters but LEN returns six, there is an extra character. LEN will not identify which character is unusual, and a nonbreaking space can require a separate cleanup step.

For ordinary leading and trailing spaces, use TRIM in a helper column, then count the cleaned values. With imported text in A2:A6, put =TRIM(A2) in B2 and fill down. TRIM removes leading and trailing ordinary spaces and reduces repeated spaces between words to one; it does not remove every kind of invisible character. If the source contains nonbreaking spaces, a replacement step may be needed, for example with SUBSTITUTE and CHAR(160), before TRIM. Keep a copy of the original data until you have checked the cleaned results.

Distinguish numbers from text and dates

A cell can display 12 but contain the text string “12” rather than the number 12. This often happens after importing a CSV, pasting from a website, or applying a text format before entry. Number formatting can also make a value look different from what is stored. COUNTIF may behave unexpectedly when the range mixes text and numbers, so check representative cells rather than relying only on their appearance.

With actual numeric values 12, 18, 12, 7, and 18 in A2:A6, =COUNTIF(A2:A6,12) returns 2. If entries were imported as text, converting them to numbers can make later calculations more dependable. One option is to select the affected cells and use the warning icon’s Convert to Number command when Excel offers it. Another is Data > Text to Columns, then finish the wizard with the appropriate settings. Confirm the result before applying a conversion to important data.

Dates are stored as serial numbers, even though Excel displays them in a date format. For a date interval, use comparison criteria built from DATE rather than relying on a displayed date string. Suppose A2:A5 contains the actual dates December 31, 2025; January 1, 2026; January 15, 2026; and February 1, 2026. The formula =COUNTIF(A2:A5,">="&DATE(2026,1,1)) returns 3. If the cells contain text that merely looks like dates, this comparison may not work as intended; convert the entries to real Excel dates first.

Use wildcards carefully

COUNTIF supports two wildcard characters in text criteria. An asterisk (*) matches any number of characters, including none; a question mark (?) matches exactly one character. For instance, with codes AX1, AX2, BX1, and AX10 in A2:A5, =COUNTIF(A2:A5,"AX*") returns 3 because all three codes beginning with AX match. Wildcards are useful for prefix, suffix, or partial-text searches, but they can also make a criterion broader than intended.

To count a literal asterisk or question mark, precede it with a tilde (~). With the entries Item*, ItemA, and Item? in A2:A4, =COUNTIF(A2:A4,"Item~*") returns 1 because the tilde makes the asterisk a literal character. If an imported identifier contains wildcard symbols, forgetting this escape can cause unexpected matches.

A blank-looking cell also deserves care. COUNTIF with an empty criterion can count cells that are truly empty and, depending on the data, cells that contain formulas returning an empty string. To count cells that are not blank, use the criterion “<>” and test the result against the specific layout. If you need to distinguish genuinely empty cells from formulas returning an empty string, COUNTIF alone may not express that distinction precisely.

Investigate zero, wrong totals, and formula errors

A result of zero is not automatically an error: it means Excel found no cells matching the criterion it evaluated. First test one known matching cell, inspect its actual contents, and simplify the condition. For example, if a broad wildcard criterion finds matches but an exact text criterion does not, investigate extra spaces or added characters. If neither works, verify that the range contains the expected data and is not pointing to another sheet or workbook.

If a total is lower than expected, check whether the range omits rows, whether values are stored as text, and whether the criterion is too narrow. If it is higher, check for duplicate records, wildcard overmatching, and formulas that return blank text. COUNTIF counts matching cells, not unique values: two identical entries count twice. For a distinct-item count, another method is needed, such as a suitable dynamic-array formula in supported Excel versions.

If the result does not change after editing the source cells, check calculation mode. In Excel for Microsoft 365 on Windows, choose Formulas > Calculation Options > Automatic. Press F9 to recalculate formulas when calculation is set to Manual. On Mac, use Formulas > Calculation Options if available in the current build, or open Excel > Settings > Calculation and set workbook calculation to Automatic. Excel for the web generally recalculates automatically, but a workbook opened in desktop Excel with manual calculation can make results appear stale.

A #VALUE! error can occur when COUNTIF refers to a range in a closed external workbook; open the source workbook and recalculate, or keep the referenced workbook available while working. A formula that shows the text of the formula rather than a result is more likely a cell-format or entry issue than a COUNTIF calculation issue. For a formula that returns a number but seems wrong, use Formulas > Evaluate Formula to step through how Excel interprets it.

Choose COUNTIF or another counting function

Use COUNTIF when one condition is enough, such as counting “Open” statuses or values above a threshold. Use COUNTIFS when every counted row must satisfy multiple conditions. For example, counting both a region and a status requires two criteria ranges rather than combining conditions into one COUNTIF. Each criteria range in COUNTIFS should cover the same number of rows and align with the other ranges.

Use COUNTBLANK when the question is simply how many cells are empty, and COUNTA when you want to count nonempty cells. These functions answer different questions from COUNTIF with a condition. To count distinct values, Excel for Microsoft 365 and Excel 2021 or later support dynamic-array functions such as UNIQUE; older versions may need a different approach. Do not switch functions just because a COUNTIF result surprises you: first establish whether the task is one condition, multiple conditions, blanks, or unique records.

Frequently Asked Questions

Why does COUNTIF return 0 when the text looks identical?
Check for leading or trailing spaces, nonbreaking spaces, and extra characters. COUNTIF ignores differences in capitalization, but visually similar text is not always identical. Use LEN on a suspect cell and compare the source with a cleaned copy.

Is COUNTIF case-sensitive?
No. COUNTIF treats uppercase and lowercase text as equivalent, so Apple and apple match the same text criterion. A case-sensitive count needs a different formula approach; COUNTIF alone cannot enforce case sensitivity.

Why does COUNTIF not update when I change a cell?
Calculation may be set to Manual. In Windows Excel, check Formulas > Calculation Options > Automatic, then recalculate with F9 if necessary. Also confirm that the edited cell lies inside the formula’s range.

Can COUNTIF count cells containing part of a word?
Yes. Use an asterisk on either side of the text to match a substring, such as a criterion shaped like *text*. Remember that * matches any number of characters, so it can return more matches than an exact criterion.

Can COUNTIF count with two conditions?
No. COUNTIF takes one condition. Use COUNTIFS for multiple conditions, with a separate range and criterion for each condition, or reconsider whether the counting question can be expressed as one criterion.

Why does COUNTIF count a cell that looks blank?
The cell may contain a formula that returns an empty string, spaces, or another invisible character. Check the formula bar and test the cell contents. A blank-looking display does not always mean the cell is truly empty.

A quick troubleshooting order

When COUNTIF does not work, make one check at a time rather than rewriting the whole formula. Confirm that the function is spelled correctly and the range covers the data; verify whether the criterion is text, a number, or a comparison; inspect one expected match for spaces or text-versus-number differences; then check wildcards and calculation mode. This sequence often reveals whether the issue is the formula, the underlying data, or simply the result Excel has been asked to calculate.

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.