Excel COUNTIF Error? Fix Common Formula Errors Fast

Errors & fixes · Updated November 19, 2026

Excel COUNTIF errors usually come from a misspelled function, an invalid reference, or criteria that do not match the cell contents as expected. With values in A2:A20 and the word Paid in some cells, enter =COUNTIF(A2:A20,"Paid") to count exact matches; if the formula itself displays an error, check the syntax and references first.

What COUNTIF does

COUNTIF counts cells in one range that meet one condition. It is useful for questions such as how many orders are marked Paid, how many scores exceed a threshold, or how many cells contain a particular word. It returns one count rather than a separate result for each row.

With statuses in A2:A20 and the status to find typed in D2, use =COUNTIF(A2:A20,D2). If seven cells match the value in D2, the result is 7. The criterion can be typed into the formula, as in the opening example, or supplied by a cell reference, which is often easier to update and audit.

COUNTIF is not case-sensitive: Paid and PAID are treated as the same criterion. It can match exact values, comparisons, and text patterns that use wildcards. It cannot apply two independent conditions; use COUNTIFS for that task.

Check the syntax and argument order

The function’s general structure is COUNTIF(range, criteria). The first argument is the set of cells to inspect, and the second is the condition each cell must satisfy. Reversing the arguments, leaving out a separator, or using an unmatched parenthesis can cause Excel to reject the formula or interpret it differently than intended.

For example, with quantities in B2:B50 and a threshold of 10, enter =COUNTIF(B2:B50,">10") to count quantities greater than 10. The comparison operator is part of the criterion, so it must be enclosed in quotation marks when written directly in the formula.

Excel for Microsoft 365 on Windows typically uses commas between arguments, as shown here. Some regional settings use semicolons as the list separator. If a formula copied from a comma-based example is rejected, check the computer’s regional list-separator setting; the function name and logic are the same, but the separator may need to match the setting. On Mac, Excel also follows the applicable regional settings.

Diagnose the error message

A result of zero is different from a formula error. Zero means Excel evaluated the function but found no matches; an error such as #NAME? or #VALUE! means the formula could not be evaluated normally. Start by reading the exact message, then inspect the formula bar for spelling, quotation marks, parentheses, separators, and references.

  • #NAME? commonly indicates that Excel does not recognize part of the formula. Check that COUNTIF is spelled correctly and that text criteria are enclosed in straight double quotation marks. A word typed without quotation marks may be treated as a named range.
  • #VALUE! can arise from invalid argument values or some external-reference situations, including a COUNTIF reference to a workbook that is closed. Verify that the range is valid and, if the formula refers to another workbook, open that workbook and recalculate.
  • Formula shown instead of a result: the cell may be formatted as Text, or Show Formulas may be enabled. Change the cell format to General, then re-enter the formula. On Windows, Ctrl+` toggles Show Formulas; on Mac, the equivalent shortcut is Control+` in many Excel configurations, though keyboard layouts can differ.
  • Zero when matches seem present: examine the actual cell values, hidden spaces, punctuation, and the selected range. A number stored as text, a date-time value with a hidden time component, or a criterion pointed at the wrong cell can explain an unexpected zero.

Errors located in the data range are also worth inspecting. Do not assume a COUNTIF result is a reliable error audit: first identify whether cells in the source range contain errors, and decide whether those cells should be excluded or handled separately. A criterion cell that itself contains an error can also prevent the intended comparison from working.

Make text criteria match the data

For an exact text match, the criterion needs to match the cell’s text apart from letter case. Leading or trailing spaces matter. A cell that looks like Paid might actually contain Paid followed by a space, often because the value was pasted or imported. Double-clicking a sample cell or checking the formula bar can reveal some discrepancies; TRIM can help clean ordinary extra spaces in a separate helper column.

COUNTIF recognizes two wildcard characters in text criteria: an asterisk matches any sequence of characters, while a question mark matches one character. With product descriptions in A2:A100, =COUNTIF(A2:A100,"*apple*") counts cells containing apple anywhere in the text. With codes in A2:A100, =COUNTIF(A2:A100,"A?7") matches a three-character pattern that starts with A and ends with 7.

To search for an asterisk or question mark as a literal character rather than a wildcard, precede it with a tilde. With notes in A2:A100, =COUNTIF(A2:A100,"~*" ) counts cells whose value is the literal asterisk. In actual formulas, remove the optional visual space before the closing parenthesis if desired; the valid compact form is =COUNTIF(A2:A100,"~*"). This counts a cell whose entire content is an asterisk, not every cell that merely contains one. To find a literal asterisk anywhere in a longer note, use =COUNTIF(A2:A100,"*~**").

When criteria are assembled from cell values, use concatenation for comparison operators. With scores in B2:B100 and a cutoff in D2, use =COUNTIF(B2:B100,">"&D2). The ampersand joins the greater-than operator to the cutoff value, so changing D2 updates the test without editing the formula.

Count numbers and dates correctly

Numeric criteria can be exact values or comparisons. With quantities in B2:B50, =COUNTIF(B2:B50,10) counts values equal to 10, while =COUNTIF(B2:B50,">=10") counts values at least 10. Excel stores dates as serial numbers, so date comparisons work best when the criterion is built from a real date value rather than typed as ambiguous text.

With dates in C2:C100 and a date in D2, use =COUNTIF(C2:C100,">="&D2) to count dates on or after D2. Make sure the cells in the range contain actual Excel dates, not date-looking text. If imported values are text, changing the display format alone does not convert them; use a suitable conversion method or re-import the data as dates.

A frequent date-counting surprise is that a cell can contain both a date and a time. If the target date is in D2 and the date-time entries are in C2:C100, =COUNTIFS(C2:C100,">="&D2,C2:C100,"<"&D2+1) counts entries from that calendar day, including times after midnight, but excludes the following day. The lower boundary is inclusive and the next-day boundary is exclusive.

Verify the range and the source data

Check that the range covers the records you intend to count and that it does not include a heading that could accidentally match a broad text criterion. Fixed references can prevent the range from moving when a formula is copied. With statuses in A2:A20 and the criterion in D2, =COUNTIF($A$2:$A$20,D2) keeps the inspected range fixed as you copy the formula down.

Look for nonprinting characters and inconsistent data types in imported or manually combined lists. A normal space is visible only indirectly, and copied text may contain nonbreaking spaces that TRIM does not remove. Numbers stored as text can also behave differently from numeric values in criteria. If only a few records fail to match, compare a failing cell with a known matching cell, inspect their formula-bar contents, and standardize the source data rather than making the criterion increasingly complicated.

Filters and hidden rows do not make COUNTIF a visible-rows-only count: it counts matching cells in the specified range whether those rows are currently visible or hidden. If the requirement is to count only rows shown after filtering, COUNTIF alone is not the right tool; the approach depends on the table layout and whether rows are hidden manually or by a filter.

Choose COUNTIFS or another approach when needed

Use COUNTIFS when each counted row must satisfy more than one condition. With regions in A2:A100 and order statuses in B2:B100, =COUNTIFS(A2:A100,"West",B2:B100,"Paid") counts rows where the region is West and the status is Paid. Each criteria range must correspond to the same rows and have the same dimensions as the other criteria ranges.

For one condition, COUNTIF is shorter and clearer. For two or more conditions, COUNTIFS is usually the natural choice. If criteria require case-sensitive matching or more complex row-by-row logic, a different formula design may be needed; do not expect COUNTIF to distinguish uppercase from lowercase or to perform several unrelated tests by itself.

If the goal is to count blank cells, COUNTIF can use an empty-text criterion. With entries in A2:A100, =COUNTIF(A2:A100,"") counts cells Excel treats as blank, including cells containing formulas that return an empty string. That distinction matters: a formula-empty cell is not physically empty, but it can still meet this COUNTIF criterion.

A practical troubleshooting sequence

When a COUNTIF formula does not behave as expected, work through these checks in order rather than rewriting it repeatedly:

  1. Confirm that the function is spelled COUNTIF and that the formula begins with an equals sign.
  2. Check that the first argument is the intended range and the second is the criterion.
  3. Verify the quotation marks around text and comparison criteria, and check the separators used by your Excel settings.
  4. Test the criterion against one known matching cell and one known nonmatching cell.
  5. Inspect for spaces, text-versus-number differences, date-time values, wildcard characters, or cells outside the selected range.
  6. If the formula returns an error, inspect the source references and any external workbooks; if it returns zero, focus first on why the data did not match.

This sequence helps distinguish a syntax problem from a data-quality problem. Replacing COUNTIF with a more complex formula will not fix inconsistent source values, while cleaning the data will not correct a misspelled function name.

Frequently Asked Questions

Why does COUNTIF return 0 when the word is in the cell? The visible text may contain extra spaces or different punctuation, or the formula may point to the wrong range. COUNTIF is not case-sensitive, so capitalization alone is not the cause. Check the full cell contents and whether you intended an exact match or a wildcard search.

Does COUNTIF distinguish uppercase and lowercase? No. COUNTIF treats text criteria as case-insensitive. If case-sensitive counting is essential, use a formula approach designed for case-sensitive comparison rather than relying on COUNTIF.

Why does Excel display the formula instead of calculating it? The cell may be set to Text, or Show Formulas may be on. Set the cell to General and re-enter the formula, and toggle Show Formulas if needed. A leading apostrophe before the equals sign can also make Excel treat the entry as text.

Can COUNTIF count cells that contain a word within a sentence? Yes. Use asterisks around the word as wildcards. For example, with descriptions in A2:A100, =COUNTIF(A2:A100,"*apple*") matches cells containing that character sequence anywhere in the text.

Can COUNTIF count two conditions at once? Not in one COUNTIF call. Use COUNTIFS when a row must meet multiple conditions, such as matching both a region and a status. Make sure the ranges align row for row.

Why does COUNTIF count hidden rows? COUNTIF evaluates the cells in its range, not just rows currently visible after filtering. If the task is specifically to count visible records, use an approach intended for filtered data instead of assuming COUNTIF follows the filter.

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.