COUNTIF Formula in Excel Not Working? Fixes That Help

Errors & fixes · Updated November 19, 2026

If COUNTIF in Excel is returning zero, an error, or a count that looks wrong, check the range and criterion first, then look for mismatched text, hidden spaces, wildcard characters, or a calculation setting that has not updated. Most problems come from the criterion not matching the cell values exactly as Excel stores them.

How COUNTIF works

COUNTIF counts cells in one range that meet one condition. Its syntax has two arguments: the cells to inspect and the criterion to apply. The criterion can be text, a number, a comparison, a wildcard pattern, or a reference to a cell containing the condition. COUNTIF is not case-sensitive, so it treats “Open” and “open” as the same text.

With department names in A2:A10, where “North” appears three times, this formula counts those matches:

=COUNTIF(A2:A10,"North")

The result is 3. The range and criterion are separate arguments, so a missing comma, mismatched parenthesis, or extra argument can prevent the formula from working. In Excel installations that use a regional list separator other than a comma, formulas may require semicolons instead; use the separator Excel inserts when you select arguments in the Formula Bar.

Check the range and criterion

A formula can be syntactically valid but point at the wrong cells. Confirm that the range includes the data rows you intend to count and does not accidentally include a heading or omit the last row. Also verify that a criterion reference points to the right cell. A common mistake is to enter a condition in D2 but have the formula refer to D3, which may be blank.

With statuses in A2:A20 and the status to count in D2, this formula uses D2 as its criterion:

=COUNTIF(A2:A20,D2)

If D2 contains Open, the formula counts cells in A2:A20 that contain that status. When copying a formula down or across, consider whether the range should stay fixed. With status values in A2:A20 and a condition in D2, this version locks the range so it does not shift when copied:

=COUNTIF($A$2:$A$20,D2)

Check for a misplaced colon or comma as well: a range uses a colon between its first and last cell, while the comma separates COUNTIF’s two arguments. If a formula refers to an entire column, such as A:A, make sure the intended data really is in that column. A range in another sheet needs the sheet name, for example Sheet2!A2:A20.

Fix criteria for numbers and comparisons

For a simple numeric criterion, the number can be entered directly. With scores in B2:B6 containing 12, 18, 20, 18, and 25, this counts values equal to 18:

=COUNTIF(B2:B6,18)

The result is 2. Comparison criteria such as greater than or less than must be written as text, with the operator inside quotation marks. With those same five scores in B2:B6, this counts values greater than 18:

=COUNTIF(B2:B6,">18")

The result is 2 because 20 and 25 are greater than 18; a value equal to 18 is not included. If the threshold is stored in E2 rather than typed into the formula, join the operator to that cell reference. With scores in B2:B6 and the threshold 18 in E2, use:

=COUNTIF(B2:B6,">"&E2)

This also returns 2. Putting the operator and cell reference together matters: a criterion written as ">E2" would search for that text rather than compare values to the contents of E2.

Resolve text, spaces, and wildcard mismatches

Text that looks identical on screen may differ underneath. A value may include a leading or trailing space, a nonbreaking space copied from a webpage, or a different punctuation mark. For example, “Open” and “Open ” are different strings for an exact match. Try selecting a cell and inspecting its contents in the Formula Bar. If the source data is inconsistent, clean it in a helper column with Excel’s TRIM function, or use Find and Replace when you know exactly which extra character to remove. TRIM removes ordinary leading, trailing, and repeated spaces, but it does not remove every possible nonprinting character.

Text imported from another system may also be stored with unexpected characters or as numbers formatted to look like text. Check a few cells directly, and compare them with a value typed into a blank cell. Be cautious about converting an entire column: changing text to numbers can affect identifiers such as product codes that intentionally begin with zero.

COUNTIF supports two wildcard characters in text criteria. An asterisk (*) matches any number of characters; a question mark (?) matches one character. With product labels in A2:A5 containing North Ridge, Northstar, South North, and Northern, this counts labels that begin with North:

=COUNTIF(A2:A5,"North*")

The result is 3: North Ridge, Northstar, and Northern. To find the word anywhere in the label, add an asterisk at both ends; that pattern also matches South North. To search for a literal asterisk or question mark, put a tilde (~) before it. With A2:A5 containing *, A*, ?, and plain, this counts cells whose value is a literal asterisk:

=COUNTIF(A2:A5,"~*")

The result is 1. Forgetting the tilde is a common reason a wildcard criterion returns a surprisingly large count.

Make date criteria match real dates

Excel generally stores dates as serial numbers and displays them using a date format. If imported dates are stored as text, a date comparison may return zero even when the cells look like valid dates. Check whether the cells behave like dates when you change their number format or use them in a calculation; if they remain text, convert the data before relying on date comparisons.

With real Excel dates in C2:C5—January 1, January 15, February 1, and March 1, 2026—this counts dates on or after February 1, 2026:

=COUNTIF(C2:C5,">="&DATE(2026,2,1))

The result is 2: February 1 and March 1 meet the condition. Using DATE avoids ambiguity in how a typed date is interpreted under different regional settings. If the comparison date is in E2, join the operator to the cell reference, just as with a numeric threshold. Remember that a date range condition may include or exclude the boundary date depending on whether you use > or >=.

Understand blanks, errors, and recalculation

To count empty cells, use an empty-string criterion. With entries in A2:A10, this formula counts cells that Excel treats as blank:

=COUNTIF(A2:A10,"")

It can include cells containing formulas that return an empty string, even though those cells are not physically empty. That distinction explains why the result may differ from a visual check. A criterion of "<>" is commonly used to count nonblank entries, but formulas returning empty strings can make “blank” and “nonblank” checks less intuitive. If your data includes such formulas, test a small sample and decide whether you mean truly empty cells or cells displaying nothing.

If Excel displays #NAME? or a formula as plain text, inspect the entry itself. #NAME? can indicate a misspelled function name or text criterion that lacks quotation marks. If the cell shows the characters =COUNTIF(…) rather than a result, change the cell format from Text to General, then re-enter the formula. Also check that Show Formulas is not turned on; the setting is on the Formulas tab.

If the result appears stale after changing source values, check Formulas > Calculation Options > Automatic. In Manual calculation mode, Excel may not update formulas immediately; press F9 on Windows to recalculate. On Mac, use Formulas > Calculate Now if needed. An external-workbook reference can also cause problems when its source workbook is closed, so open the source file and confirm the referenced range if COUNTIF returns an error.

Choose COUNTIF or another counting method

COUNTIF is suited to one condition applied to one range. For example, it can count orders with the status Open, or scores above a threshold. When a task needs two or more conditions—such as counting only Open orders for one region—COUNTIFS is the more direct choice because it accepts multiple range-and-criteria pairs. Make sure each criteria range covers the same rows as the other ranges so the conditions align record by record.

If you need case-sensitive matching, COUNTIF is not enough because it ignores letter case. If you need to count rows meeting a more complex condition, such as checking a calculated expression or combining several tests, a helper column or another suitable function may be clearer. For a one-condition count, though, COUNTIF is usually easier to audit than a more elaborate formula.

For data that grows over time, placing the records in an Excel Table can make ranges easier to maintain: table references expand as rows are added. With a table named Orders and a Status column, this counts rows marked Open:

=COUNTIF(Orders[Status],"Open")

The result is the number of table rows whose Status value matches Open. If your table or column has a different name, use its actual name; Excel can insert a structured reference when you select the column while building the formula.

A quick troubleshooting sequence

When the count still looks wrong, work through these checks in order rather than rewriting the formula repeatedly:

  • Confirm the function is COUNTIF and that the formula has a range, a comma or regional list separator, and a criterion.
  • Select the range in the formula and verify that it covers the intended records and worksheet.
  • Check whether the criterion is an exact text or number match, a comparison, or a wildcard pattern.
  • Inspect source cells for extra spaces, imported text, inconsistent punctuation, or formulas that display an empty string.
  • Check the cell format, formula display setting, and calculation mode if Excel shows text, an error, or an old result.
  • Test the formula against a few known records and calculate the expected count manually.

A small controlled check is often the fastest way to isolate the cause. Copy a few representative values into a temporary range, use a simple exact-match criterion, and see whether Excel counts them as expected. Once the basic match works, add the real range or more complicated criterion back in.

Frequently Asked Questions

Why does COUNTIF return 0 when the value is there?
Check for extra spaces, a text-versus-number mismatch, a criterion pointing at the wrong cell, or a range that excludes the value. For wildcard criteria, confirm that * and ? are being used intentionally.

Is COUNTIF case-sensitive?
No. COUNTIF treats uppercase and lowercase versions of the same text as matches. Use another approach if the distinction between Open and OPEN matters.

How do I count cells that contain part of a word?
Use asterisk wildcards around the text in the criterion. Remember that * matches any number of characters, while ? matches exactly one character. Use ~ before a wildcard when you want to match the character literally.

Why does COUNTIF show the formula instead of a number?
The cell may be formatted as Text, or Show Formulas may be enabled. Set the cell to General and re-enter the formula, and check the Formulas tab for the formula-display setting.

Can COUNTIF count dates in a range?
Yes, provided the cells contain real Excel dates rather than text that merely looks like dates. Use a comparison criterion such as on or after a date, and use DATE(year,month,day) when you want to avoid regional date-entry ambiguity.

Related

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.