If COUNTIF in Excel is not counting correctly, check the criteria, the cell contents, and the range first: extra spaces, wildcard characters, mismatched data types, or a range that stops too soon are common causes. Make a small test with a known match, then compare the result with the cells you expect to count.
How COUNTIF works
COUNTIF counts cells in one range that meet one condition. Its two arguments are the range to examine and the criterion to apply. The criterion can be text, a number, a comparison such as greater than a value, or a wildcard pattern. It returns a number, including zero when no cells meet the condition.
With product names in A2:A20, this counts cells whose entire value is Apples:
Assuming product names are in A2:A20, use: =COUNTIF(A2:A20,"Apples")
COUNTIF is not case-sensitive, so that example also matches APPLES or apples. By default, ordinary text criteria match the whole cell rather than a piece of text inside it. Wildcards change that behavior, which is useful but can also make a count look unexpectedly high.
COUNTIF handles only one condition at a time. If you need to count records that meet two or more conditions—for example, a particular product and a particular region—use COUNTIFS instead. Every criteria range in COUNTIFS should have the same dimensions as the other criteria ranges.
Check that the range includes the right cells
A correct criterion still returns a misleading answer if the range is incomplete or pointed at the wrong column. Click the cell containing the formula and inspect its colored range outline, or select the range in the formula bar. Look for a first or last row that was left out when data was added, a heading included by mistake, or a reference to a different worksheet than the one containing the records.
For instance, if a formula checks A2:A20 but new entries have been added in rows 21 through 30, those entries cannot affect its result. Update the range or convert the data into an Excel table so formulas can use a column that expands as table rows are added. If you copy a formula down, also check whether its range references move as intended. A dollar sign makes a reference absolute; without one, references may shift when copied.
Filtering a list does not normally make COUNTIF count only the visible rows. COUNTIF evaluates the cells in its specified range, including rows hidden by a filter. If the question is specifically about visible records, COUNTIF alone may not be the right tool; use a method designed for visible cells or add a condition that identifies the records you want.
Look for spaces, hidden characters, and similar text
Two cells can look identical on screen but contain different text. A value copied from a website, exported system, or email may have a leading space, a trailing space, or a nonbreaking space. For example, “North” and “North ” are not the same text value for an exact whole-cell match. Other invisible characters can also interfere with matching.
Test a suspicious cell by clicking it and reading the formula bar, or compare it with a freshly typed version of the expected value. You can also check the character count. Assuming the suspect text is in A2, this returns the number of characters in that cell:
Assuming the text to inspect is in A2, use: =LEN(A2)
TRIM can remove ordinary leading and trailing spaces and reduce repeated internal spaces, but it does not remove every kind of invisible character. CLEAN removes certain nonprinting characters, but it does not remove all Unicode characters either. For imported data, a cleanup column or carefully checked Find and Replace may be needed. Keep the original data until you have confirmed the cleaned results, especially if spaces may be meaningful.
For a partial match, wildcards can help. An asterisk matches any number of characters, while a question mark matches one character. Assuming descriptions are in A2:A20, this counts cells containing the word apple anywhere in the text:
Assuming descriptions are in A2:A20, use: =COUNTIF(A2:A20,"*apple*")
That pattern can also count unintended matches, such as “pineapple.” If you need a whole-word match, a broad wildcard pattern is not enough by itself; consider standardizing the data or using a more specific method. To search for a literal asterisk or question mark, precede the wildcard character with a tilde in the criterion, such as ~* for a literal asterisk.
Review criteria syntax and cell references
Comparison operators must be part of the criterion. When the comparison value is typed directly, put the operator and value together inside quotation marks. Assuming scores are in B2:B20, this counts values greater than 80:
Assuming scores are in B2:B20, use: =COUNTIF(B2:B20,">80")
If the threshold is stored in a cell, join the operator to that cell reference with the ampersand. Assuming scores are in B2:B20 and the threshold is in D1, this counts scores above the value in D1:
Assuming scores are in B2:B20 and the threshold is in D1, use: =COUNTIF(B2:B20,">"&D1)
A common mistake is writing the operator inside quotation marks but leaving the linked value out of the expression, or typing a comparison as plain text without the operator. Also check for a criterion that references the wrong cell after the formula was copied. If the criterion should always use D1, make that reference absolute before copying the formula.
For more than one condition, use COUNTIFS. Assuming product names are in A2:A20 and regions are in B2:B20, this counts rows where the product is Apples and the region is West:
Assuming products are in A2:A20 and regions are in B2:B20, use: =COUNTIFS(A2:A20,"Apples",B2:B20,"West")
COUNTIFS applies all its listed conditions to the same row. It is not a substitute for adding separate COUNTIF results when you want either condition to be true; for an “Apples or Pears” count, separate counts or a suitable combined approach may be needed.
Check numbers, dates, and blank-looking cells
Numbers and dates can be stored or displayed in ways that make their values confusing. A cell formatted to show zero decimal places may contain 12.4 while displaying 12. A date may display as “Jan 1” while its underlying value includes a year. A number imported as text may look like a number but behave differently in other calculations. Check the formula bar, number format, and source data before deciding the criterion is wrong.
Excel stores actual dates as serial numbers, so date comparisons should use real date values rather than relying on how a date is displayed. Assuming dates are in C2:C100, this counts dates on or after January 1, 2026:
Assuming actual Excel dates are in C2:C100, use: =COUNTIF(C2:C100,">="&DATE(2026,1,1))
If the source cells contain text that resembles dates, this date comparison may not behave as expected. Convert the source to actual dates before counting, and verify that the conversion worked. Date formats can vary by regional settings, so avoid assuming that an imported string such as 03/04/2026 has the same interpretation everywhere.
A blank-looking cell is not always truly empty. It may contain a formula that returns an empty string, or it may contain a space. To count empty cells, assuming the range is A2:A20, use:
Assuming the cells to check are in A2:A20, use: =COUNTIF(A2:A20,"")
This is useful for finding empty-looking entries, but it does not tell you why they look empty. Inspect a few of the counted cells to distinguish genuinely blank cells from formulas or hidden spaces. If the count is for missing required information, define what “missing” means for the dataset before relying on the result.
Use a short troubleshooting sequence
When the answer is still puzzling, isolate the problem rather than changing several parts of the formula at once. A systematic check makes it easier to tell whether the range, data, or criterion is responsible.
- Choose one cell that should match and one that should not. Confirm their displayed contents and inspect them in the formula bar.
- Temporarily test a criterion that exactly matches a simple, known value. If that count is wrong, focus on the selected range and the stored contents.
- Check the range’s first and last rows, worksheet name, and copied references. Confirm that the cells you expect are inside the specified range.
- Check for spaces, hidden characters, wildcard characters, and differences between text and numeric or date values.
- Review comparison operators and any cell reference used to build the criterion. Make sure the referenced threshold contains the value you expect.
- If the displayed result may be stale, check Formulas > Calculation Options > Automatic. In manual calculation mode, formulas may not update until recalculation.
One useful diagnostic is to select the formula cell and press F2, then Enter. This re-enters the formula and can reveal a syntax issue, but it will not clean the source data or fix a range that omits rows. In Excel for Mac, the function keys may require the Fn key depending on the keyboard’s settings. Avoid treating recalculation as a fix until you have checked the actual inputs.
When another counting method is a better fit
Use COUNTIF when you need one condition, such as counting a particular status or values above a threshold. Use COUNTIFS when each counted row must meet multiple conditions. If you need case-sensitive text matching, COUNTIF is not sufficient because its text comparisons are case-insensitive; a different formula or a data-cleaning approach is needed.
For a count based on visible filtered rows only, or a complex rule involving several kinds of logic, consider a method designed for that requirement rather than trying to stretch COUNTIF. A PivotTable can summarize categories and is useful for exploring a larger list, although it needs refreshing in some workflows after source data changes. The best alternative depends on whether the goal is a live formula, a filtered subtotal, a multi-condition count, or an interactive summary.
Before replacing a formula, write the rule in plain language: “count every row where status is Open,” for example, or “count visible rows with a date in this month.” That distinction often makes the right function or summary tool clear and prevents an apparently incorrect result from being caused by an unstated expectation.
Frequently Asked Questions
Why does COUNTIF return zero when the text is visibly there?
Check that the range includes the cell and that the criterion matches its full value. Leading or trailing spaces, nonbreaking spaces, hidden characters, or a partial-text expectation can explain the mismatch. Try a controlled test with a freshly typed value.
Does COUNTIF distinguish between uppercase and lowercase?
No. COUNTIF text matching is case-insensitive, so uppercase and lowercase versions of the same text are treated as matches. Use a different approach if capitalization must be significant.
Why is COUNTIF counting too many cells?
Look for wildcard characters such as * or ? in the criterion, a range that includes extra rows, or values that contain the searched text as part of a longer string. A pattern like *apple* can include “pineapple,” not just “apple.”
Can COUNTIF count cells that contain a word anywhere?
Yes. Use an asterisk before and after the word to match text on either side. Remember that this is a partial match and may include longer words or phrases containing the same characters.
Why does the count not update when I change the data?
Check Formulas > Calculation Options > Automatic, and confirm that the changed cell is inside the formula’s range. In manual calculation mode, Excel may leave formula results unchanged until recalculation.
Can COUNTIF count by two conditions?
Not with a single COUNTIF condition. Use COUNTIFS when each row must meet multiple conditions, and make sure its criteria ranges have matching sizes and align row by row.
Related
- Closing Checklist Template Excel: Set Up and Use It
- How to Close the Queries & Connections Pane in Excel
- Cleaning Checklist Template Excel: Setup and Tips for Teams
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Why Block Sender Does Not Work in Outlook and How to Fix ItBlock Sender in Outlook usually does not stop a message from being sent or make it disappear; it…
- Excel Worksheet Keeps Crashing? Causes, Checks and FixesIf an Excel worksheet keeps crashing, first find out whether Excel fails with every workbook or only one,…
- Average Formula Not Working in Excel? Fix ItIf the values are in B2:B5, enter =AVERAGE(B2:B5); with 10, 20, 30, and 40 in those cells, the…
- Excel Flash Fill Not Working? Fix the Most Common CausesIf Flash Fill is not working in Excel, enter a clear example beside your source data, select the…
- Why Is IRR Not Working in Excel? Causes and Fixes ExplainedIRR in Excel usually fails because the cash-flow range has the wrong signs or data, the cash flows…
- Excel Text Box Disappeared? How to Find and Restore a Missing BoxAn Excel text box that disappears is often still in the workbook: it may be hidden in the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.