COUNTIF usually appears not to work because its criteria do not match the cells exactly, the data contains hidden spaces or inconsistent types, or Excel is treating the formula as text. Check the formula’s range and criteria first, then inspect the underlying values; 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 structure is =COUNTIF(range, criteria): the range is where Excel looks, and the criteria tells it what to count. The function returns a number, including zero when no cells match. It does not add the values in those cells; use SUMIF when the goal is to total values that meet a condition.
For example, with product names in A2:A6 and the values Apple, Pear, Apple, Orange, and Apple, this formula counts the exact entries Apple:
With those product names in A2:A6, =COUNTIF(A2:A6,"Apple") returns 3.
COUNTIF is not case-sensitive, so Apple and APPLE match the same criterion. By default, a text criterion such as Apple matches that text as a whole cell value, not every cell that merely contains those letters. Wildcards change that behavior, as explained below.
Start with a quick diagnostic
Before changing the data, check that the formula is in a cell outside the range being counted and that the range covers the intended rows. Then confirm the result is a genuine formula: it should begin with an equals sign and should not have an apostrophe before it. If it returns zero, test a simple, known value from the range as the criterion.
A practical check is to select one cell you expect to count and compare its content with the criterion. Look for extra spaces, a different spelling, a nonbreaking space copied from a website, or a value stored as text instead of a number. The cell may look right on screen while its underlying content differs.
- If the result is zero, investigate the criteria and the actual cell contents.
- If Excel displays the formula itself, investigate cell formatting or formula-display mode.
- If Excel shows an error, check the function name, parentheses, argument separators, and referenced cells.
- If the number is too high or too low, check the selected range and whether the criterion is exact or uses a wildcard.
Check criteria syntax and operators
Text criteria need quotation marks inside the formula. If a criterion is typed as a word without quotation marks or a cell reference, Excel may interpret it as a defined name rather than text and return an error. The criterion can also refer to a cell: in that case, use the cell reference as the second argument, without putting quotation marks around the reference.
To count values that meet a comparison, put the operator and value together inside a text criterion. For example, with ages in B2:B20, the following counts values greater than 18:
With ages in B2:B20, =COUNTIF(B2:B20,">18") returns the number of cells whose numeric value is greater than 18.
When the threshold is in another cell, join the operator to the cell reference with the ampersand. With ages in B2:B20 and a threshold in D1, =COUNTIF(B2:B20,">"&D1) counts values greater than the threshold in D1. The result depends on the data and threshold; for example, if D1 contains 18, it counts ages above 18. A common mistake is placing the operator outside the quoted criterion or forgetting the ampersand when joining it to a cell reference.
For text that should match exactly, avoid adding wildcard characters. To count cells that begin with North, use an asterisk after the word. With region labels in A2:A6 containing North, North Depot, South, Northwest, and a blank, =COUNTIF(A2:A6,"North*") returns 3. An asterisk matches any sequence of characters; a question mark matches one character. Put a tilde before a wildcard when you need to search for the wildcard character itself.
Look for spaces and inconsistent text
Leading or trailing spaces are a frequent reason a visible label does not match the criterion. For example, a cell containing Apple followed by a space may not match a criterion for Apple. Pasted data can also include nonbreaking spaces, which ordinary trimming may not remove. Check a suspicious cell in the formula bar, or compare its length with a clean example; if the source is imported, inspect a few values rather than assuming every row is consistent.
For ordinary extra spaces, create a cleaned helper column using TRIM, then use COUNTIF on that helper column. With imported text in A2, =TRIM(A2) removes leading and trailing ordinary spaces and reduces repeated spaces between words to one. Fill it down, then count the cleaned results. TRIM does not remove every unusual whitespace character, including some nonbreaking spaces, so copied web data may need further cleaning or a targeted replacement.
Spelling and punctuation matter too: a hyphen, apostrophe, or different label is part of the cell value. COUNTIF ignores letter case, but it does not treat otherwise different words as equivalent. If the data has several accepted spellings, either standardize the source values or count each allowed spelling separately and combine the counts.
Check numbers stored as text and date values
A cell that looks like 25 may contain the number 25 or the text characters 2 and 5. Changing its number format does not necessarily convert text into a number. If COUNTIF gives an unexpected result for numeric criteria, look for Excel’s number-stored-as-text warning, check how the values align, or test a small sample. Where appropriate, convert the source values to numbers, for example with the warning’s Convert to Number option or Data > Text to Columns, and verify the result before relying on the count.
Dates require similar care. Excel stores real dates as serial numbers and displays them using a date format; a date-looking text string is not necessarily a real date. For a date comparison, build the criterion from a date value rather than relying on how the date is displayed. Suppose B2:B6 contains real dates of January 1, January 15, February 1, and January 31, 2026, followed by the text Jan 20. With those entries in B2:B6, =COUNTIF(B2:B6,">="&DATE(2026,1,1)) returns 4: it counts the four real dates on or after January 1, not the text entry. To count a date interval, use COUNTIFS with a lower and upper boundary, taking care to make the end boundary inclusive or exclusive as intended.
Fix formulas entered as text or showing errors
If a cell displays =COUNTIF(A2:A20,"Apple") instead of a result, Excel may be treating the entry as text. Select the formula cell, change its number format to General, then edit the cell and confirm the formula again. In Excel for Microsoft 365 on Windows, the format control is on Home > Number Format. A leading apostrophe can also force text; remove it. If many formulas display instead of results, check whether Show Formulas is enabled on the Formulas tab.
A formula that returns an error rather than a count calls for a different check. Confirm that COUNTIF is spelled correctly, that the parentheses are balanced, and that the range and criteria arguments are present. Excel’s argument separator depends on regional settings: many installations use commas, while some use semicolons. If Excel rejects a formula copied from another region, replace the separators with the ones your installation expects. Do not change a comma inside quoted text just because it appears in a criterion.
Also check for circular references. If the COUNTIF formula is placed inside the very range it counts, the formula may include its own cell and cause a circular calculation. Move the formula outside the counted range or adjust the range so it excludes the result cell.
Verify the range and calculation settings
COUNTIF only examines the range specified in its first argument. A formula that ends at row 100 will not count a matching value added in row 101. Likewise, a range shifted by one row may compare the wrong records or omit a header-adjacent entry. Select the range reference in the formula bar and check its endpoints against the data. If records are added regularly, an Excel table can make formulas easier to maintain because its column expands as rows are added.
When the formula looks correct but a result does not update after data changes, check calculation mode. In Excel for Microsoft 365 on Windows, use Formulas > Calculation Options > Automatic. In manual calculation mode, formulas may wait for recalculation. Excel for Mac also has calculation options on the Formulas tab, though ribbon layout can vary slightly by release. COUNTIF itself is available in Excel for Microsoft 365; it is not a newer function that requires a recent version.
When COUNTIF is not the right function
Sometimes COUNTIF is working correctly but is not suited to the question. It accepts one range and one criterion. Use COUNTIFS when a count must satisfy multiple conditions, such as a department and a date range. Use SUMIF or SUMIFS when you want to add matching amounts rather than count matching rows. For a case-sensitive count, COUNTIF is not enough by itself because it treats uppercase and lowercase letters as equivalent; a formula using EXACT with SUMPRODUCT can provide a different approach, but it needs careful testing with the actual data.
Also distinguish an exact match from a contains search. If the goal is to count any cell containing the word Apple among other text, use wildcards around the criterion; if the goal is to count only cells whose entire value is Apple, leave them out. Choosing the intended meaning first often resolves what looks like a formula bug.
Frequently Asked Questions
Why does COUNTIF return zero when the text is visibly there?
Check for extra or unusual spaces, different punctuation, a misspelling, or a range that excludes the cell. COUNTIF ignores letter case, so capitalization alone is not the cause. A wildcard criterion can count partial matches when that is what you intend.
Does COUNTIF distinguish uppercase from lowercase?
No. COUNTIF is not case-sensitive, so Apple and APPLE satisfy the same text criterion. Use a case-sensitive method if capitalization must be treated as significant.
How do I count cells that contain a word, not just equal it?
Use an asterisk before and after the word in the criterion. The asterisks stand for any characters, so the match can occur within a longer cell value. Check that this broader match will not include unwanted entries.
Why is Excel showing my COUNTIF formula instead of its answer?
The cell may be formatted as Text, the formula may have a leading apostrophe, or Show Formulas may be enabled. Change the cell to General, re-enter the formula, and check the Show Formulas setting if other formulas are also visible.
Can COUNTIF count dates or numbers stored as text?
It can count values according to their actual contents, but text that looks like a date or number may not behave like a true Excel date or number in comparisons. Convert inconsistent entries to the appropriate data type, then verify a few cells and retest the count.
Related
- To Do List Excel Sheet Template: Make a Useful Tracker
- To-Do List Excel Spreadsheet: Setup, Columns and Tips
- Task List Template Excel Spreadsheet: Set Up and Use
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Not Responding: Fix Margins That Do Not Fit a PageIf Excel says the margins do not fit the page and then stops responding, first give it a…
- Excel LET Not Working? Common Errors and How to Fix ThemExcel LET usually fails because the installed Excel version does not support it, the formula has the wrong…
- Hyperlink in Word Not Working? Fixes That Usually HelpIf a hyperlink in Word is not working, first try following it with Ctrl+click on Windows or Command+click…
- Excel Cannot Group Dates? Fix the PivotTable ErrorIn Excel for Microsoft 365 on Windows, the PivotTable message “Cannot group that selection” usually means the date…
- Outlook /restore Not Working? Fix the Window StateOutlook’s /restore switch is a startup option for classic Outlook for Windows: it asks Outlook to restore its…
- Bold Formula Not Working in Excel? How to Fix ItExcel formulas do not make text bold by themselves: a formula calculates a value, while bold is cell…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.