COUNTIF With a Cell Reference Not Working? Fixes

If COUNTIF is not using your cell reference, check that the reference is not inside quotation marks: with values in A2:A8 and the search term North in D2, use =COUNTIF(A2:A8,D2), which returns 3 if North appears three times. Quotes around D2 make Excel look for the literal text “D2,” not the value stored in that cell.

How COUNTIF reads a cell reference

COUNTIF has two arguments: the range to examine and the criterion to apply. The criterion can be a value, text, an expression such as a comparison operator plus a value, or a reference to a cell containing one of those criteria. A reference lets the formula respond when you change the criterion cell, without editing the formula itself.

For example, with product names in A2:A8 and the name to find in D2, =COUNTIF(A2:A8,D2) counts cells in A2:A8 equal to the current contents of D2. If D2 contains North and North appears three times in the range, the result is 3. If you change D2 to South, the same formula counts South instead.

By contrast, with product names in A2:A8 and the intended search term in D2, =COUNTIF(A2:A8,"D2") looks for the literal text D2. It will usually return 0 unless a cell in the range actually contains those two characters. Remove the quotation marks around the reference.

Quotation marks are still necessary for fixed text typed directly into a formula. With names in A2:A8, =COUNTIF(A2:A8,"North") counts cells equal to North. A useful distinction is that quotation marks belong around text criteria you type into the formula, not around a cell address whose contents should supply the criterion.

Build criteria that include operators

A frequent source of confusion is a comparison such as “greater than the number in D2.” The operator must be combined with the referenced value to create one criterion. With quantities in B2:B8 and a numeric threshold of 100 in D2, use =COUNTIF(B2:B8,">"&D2). If the quantities are 120, 100, 80, 145, 99, 110, and 60, the result is 3: 120, 145, and 110 are greater than 100.

The ampersand joins the operator text to the value from D2. Putting the operator in quotes by itself, as in =COUNTIF(B2:B8,">D2"), makes the criterion read as text beginning with the characters >D2; Excel does not substitute the contents of D2 in that quoted string. Use the same construction for other comparisons:

With numbers in B2:B8 and a threshold in D2, =COUNTIF(B2:B8,"<="&D2) counts values less than or equal to the threshold.
With dates in A2:A20 and a cutoff date in D2, =COUNTIF(A2:A20,">="&D2) counts dates on or after that cutoff, provided the entries are real Excel dates.
With text in A2:A20 and the required prefix in D2, =COUNTIF(A2:A20,D2&"*") counts text beginning with that prefix. The asterisk represents any following characters.

In Excel, a criterion cell can also contain a complete criterion, such as >100, and be referenced directly. With values in B2:B8 and the criterion text >100 entered in D2, =COUNTIF(B2:B8,D2) applies that comparison. This can be convenient, but the operator-and-reference construction is often easier to maintain when the operator is fixed and only the threshold changes.

Check copied formulas and locked references

A formula may work in its first row but stop matching after you copy it down. Excel adjusts relative references as a formula is filled. If your search term is always in D2, copying a formula down can change D2 to D3, D4, and so on. If those cells are empty or contain different criteria, the results will change accordingly.

With data in A2:A20, a criterion in D2, and results beginning in E2, use =COUNTIF($A$2:$A$20,$D$2) if both the data range and criterion should remain fixed while you fill the formula down. The dollar signs make the references absolute. If each row should use its own criterion in D2, D3, D4, leave the criterion reference relative instead: =COUNTIF($A$2:$A$20,D2). Choose the reference style based on how the formula is meant to be copied.

To add or remove dollar signs while editing a reference, select the reference in the formula bar and press F4 in Excel for Windows. On Mac, the shortcut can depend on keyboard settings and function-key configuration; you can also type the dollar signs directly. A mixed reference locks just one part, such as $A2 (fixed column) or A$2 (fixed row), which is useful in two-way tables.

Look for mismatched data and hidden spaces

If the reference is correct but COUNTIF returns 0, compare the criterion cell with the actual entries rather than relying on how they look. A cell may contain a trailing space, an extra nonbreaking space copied from a web page, or a different character that resembles the expected one. For example, “North” and “North ” are not identical strings for an exact match.

Numbers and dates can also be stored as text. A value that looks like 100 may be text, while another cell contains the numeric value 100. A date displayed as 11/02/2026 may be a real date serial in one cell and text in another. COUNTIF criteria can behave differently when the data types are mixed, so check the source data and normalize it where appropriate. Changing a cell’s number format alone does not convert text into a number or date.

Useful checks include selecting a cell to inspect its contents in the formula bar, checking whether numbers align or sort as expected, and testing a small section of the range. If the data came from an external system, use Excel’s data-cleaning tools or a helper column to remove unwanted spaces and convert text values. Avoid changing the criterion formula repeatedly when the real problem is inconsistent source data.

Understand wildcards and exact matching

COUNTIF supports two wildcards in text criteria: * matches any number of characters, including none, and ? matches exactly one character. With names in A2:A20 and a fragment in D2, =COUNTIF(A2:A20,"*"&D2&"*") counts cells containing that fragment anywhere. If D2 contains ann, for example, this may match Ann Lee and Joann, because COUNTIF is not case-sensitive.

If the text entered in D2 itself contains * or ?, Excel treats those characters as wildcards. To search for a literal wildcard, put a tilde before it. For instance, with labels in A2:A20 and a literal asterisk in D2, =COUNTIF(A2:A20,"~"&D2) searches for a cell whose text is an asterisk. Tilde also escapes a literal question mark; two tildes represent a literal tilde.

COUNTIF does not distinguish uppercase from lowercase. If the task requires a case-sensitive count, COUNTIF alone is not the right tool; a formula using EXACT with a counting approach is more suitable. Also note that a criterion such as * may match text entries, not every possible nonblank value in the way a reader might assume. Be explicit about whether you want exact equality, a prefix, a substring, or a wildcard pattern.

Verify the range, calculation, and formula syntax

Confirm that the first argument covers the cells you intend to count. A range that stops at row 20 will not include a new entry added in row 21. If the formula is copied, check that the range has not shifted unexpectedly. A table or a deliberately expanded range can make formulas easier to maintain as data grows.

Excel normally recalculates formulas automatically. If results appear stale, check Formulas > Calculation Options and select Automatic if appropriate, then recalculate. You can also use Formulas > Show Formulas to see whether the cell contains the formula you expect rather than a displayed result. If the formula appears as plain text, check whether the cell is formatted as Text or whether an apostrophe was entered before the equals sign; change the format to General and re-enter the formula.

In English-language Excel examples, formulas use commas between arguments. Some regional settings use semicolons as argument separators. If Excel rejects a correctly structured formula, check the separator expected by your installation rather than altering the reference logic. A missing parenthesis or a quote left open can also produce an error or an unexpected criterion.

When COUNTIFS or another function is a better fit

COUNTIF handles one range and one criterion. If you need to require multiple conditions at the same time, COUNTIFS is generally the appropriate function. For example, you might count rows where a department equals the value in one cell and a status equals the value in another. Each range-and-criterion pair is supplied separately, and the ranges should cover corresponding rows.

When a criterion needs case-sensitive matching, more complex logic, or a condition COUNTIF cannot express cleanly, another formula may be more appropriate. The best choice depends on what counts as a match. Before switching functions, write down the rule in plain language—such as “exact text, regardless of case” or “date on or after the selected date”—then choose a formula that implements that rule. This avoids using a more complicated function to compensate for a simple reference typo.

Frequently Asked Questions

Why does COUNTIF return zero when the cell reference is correct?
Check for extra spaces, different data types, an incomplete range, and a criterion that does not match the actual cell contents. Also make sure the reference is not quoted as text and that a comparison operator is joined to the referenced value when needed.

Should I put quotation marks around a cell reference in COUNTIF?
No. Use a bare reference, such as D2, when the cell’s contents should be the criterion. Use quotation marks for text typed directly into the formula, such as “North”, or for the operator portion of a comparison that you join to a reference.

How do I count values greater than a number in another cell?
Join the greater-than sign to the threshold reference. If values are in B2:B20 and the threshold is in D2, use =COUNTIF(B2:B20,">"&D2). Verify that both the values and the threshold are numeric.

Why does my COUNTIF formula change when I copy it?
Relative references move when copied. Use dollar signs to lock a range or criterion cell that must stay fixed, but leave references relative when each copied row should use a different criterion.

Does COUNTIF distinguish uppercase and lowercase?
No. COUNTIF text matching is case-insensitive. If capitalization must be part of the matching rule, use a case-sensitive approach instead of relying on COUNTIF.

Can COUNTIF search for part of the text in a referenced cell?
Yes. Add asterisks around the referenced text, such as =COUNTIF(A2:A20,"*"&D2&"*"), to count cells containing it anywhere. Remember that * and ? have wildcard meanings unless escaped with a tilde.

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.