Google Sheets COUNTIF Not Working? Fix Common Causes

If Google Sheets COUNTIF is not working, first check that the range and criterion match the data exactly, then confirm the formula is being calculated rather than displayed as text. COUNTIF uses the form =COUNTIF(range, criterion); a wrong range, an unexpected space, or a criterion written in the wrong format can produce zero or an error even when matching values appear on screen.

Start by selecting the formula cell and checking the formula bar. Confirm the formula begins with an equals sign, points to the cells you intend to count, and uses a criterion that describes the stored values—not just how they look. The checks below help distinguish a genuine formula problem from a mismatch in the underlying data.

How COUNTIF works in Google Sheets

COUNTIF counts cells in one range that meet one condition. The range is the cells to inspect; the criterion is the value or rule to test. Text criteria are usually enclosed in double quotes, while a cell reference can supply the criterion directly. COUNTIF compares text without distinguishing uppercase from lowercase, so “Open” and “open” count as matches.

For example, suppose A2:A8 contains Apple, apple, pear, pineapple, a blank cell, Apple, and orange. With that layout, this formula returns 3 because it counts the three cells whose full text is Apple, ignoring capitalization:

With the fruit names in A2:A8, use =COUNTIF(A2:A8,"Apple"). The result is 3.

COUNTIF also accepts comparison criteria such as greater than or equal to a value, and it supports wildcards for partial text matches. It does not count based on font color, fill color, or whether a row is visible after filtering. If you need multiple conditions—for example, an order that is both Open and in the West region—use COUNTIFS rather than trying to combine several rules into COUNTIF.

Check the range and criterion first

A common cause of an unexpected zero is a range that stops too early, starts on the wrong row, or points to a different column from the one containing the values. Click the formula cell and inspect the colored range outline in the sheet. Compare its boundaries with the data you want counted. If your data grows over time, consider using a column range such as A2:A rather than a fixed range that may omit later rows.

Next, inspect the criterion. A quoted word such as “Open” asks for that text; a reference such as D1 uses the value in D1. If the criterion is meant to come from a cell, make sure the reference points to the intended cell and has not shifted when the formula was copied. For comparisons using a value in another cell, join the comparison operator to the cell reference with an ampersand.

With scores in B2:B6 (12, 8, 15, 15, and 21) and the threshold 15 in D1, use =COUNTIF(B2:B6,">="&D1). The result is 3, because 15, 15, and 21 meet the condition.

If you copy a formula down a column, check whether its range should stay fixed. A relative reference can move as the formula is copied; dollar signs lock a reference when needed. For instance, $A$2:$A$20 remains fixed when copied. If the formula is only in one cell, an incorrectly shifted range is less likely, but still worth checking.

Look for spaces and hidden characters

Two cells can look identical but contain different text. A leading or trailing space, a nonbreaking space copied from a webpage, or an invisible line break can prevent an exact match. COUNTIF does not automatically clean every cell before comparing it. This is why a visible “Open” may fail to match a criterion that appears to say “Open” but includes an extra character.

Click a suspicious cell and inspect its contents in the formula bar. You can also compare the text with a known clean value by retyping it in a blank cell. If you need to clean a separate copy of a value, TRIM removes ordinary leading and trailing spaces and reduces repeated spaces between words.

With the potentially inconsistent text in A2, enter =TRIM(A2) in B2 and fill it down. If A2 contains “ Open ” with ordinary spaces around the word, B2 returns “Open”. Use the cleaned column as the COUNTIF range if you want to count the normalized values. TRIM does not remove every kind of invisible character, so pasted data may need additional cleaning if the mismatch remains.

For partial matches, wildcards can help, but they change what counts. An asterisk matches any number of characters, and a question mark matches one character. In the fruit example above, with values in A2:A8, =COUNTIF(A2:A8,"*apple*") returns 4: it includes Apple, apple, pineapple, and the second Apple. That is useful for searching within text, but it can also count values you did not intend. To search for a literal asterisk or question mark, prefix the wildcard with a tilde, as in =COUNTIF(A2:A20,"~*"), which counts cells containing a literal asterisk as the criterion.

Check numbers, dates, and formatting

Formatting changes how a value appears, not necessarily what value the cell contains. A date displayed as 1/2/2026 may be stored as a date serial number; a number displayed with currency formatting is still numeric. Conversely, imported data may contain digits stored as text. When a count is unexpectedly low, check a few source cells by selecting them and looking at the formula bar, and consider whether the column mixes numbers and text.

Dates are especially easy to misread because display formats vary by locale. For date comparisons, build the criterion from a date value rather than relying on a typed date string that might be interpreted differently. With dates in A2:A10 and a start date in D1, use =COUNTIF(A2:A10,">="&D1). The result is the number of dates on or after the date stored in D1. Make sure D1 is a real date value and that the range contains dates, not text that merely looks like dates.

For numeric comparisons, the operator belongs inside the criterion text, such as “>10” or “<=25”. When the comparison value is held in a cell, join the quoted operator to the reference with &, as in the earlier example. If you are testing for an exact numeric value, a criterion can be a number or a cell reference containing that number; avoid adding quotation marks around a comparison in a way that turns the whole test into unintended text.

Fix formula display, parsing, and reference errors

If the cell shows the formula itself instead of a count, check whether the cell is formatted as plain text or whether the entry begins with an apostrophe. Select the cell, choose Format > Number > Automatic, then edit and re-enter the formula. Simply changing the format may not recalculate text already entered; press Enter after editing so Sheets interprets it as a formula.

If Sheets reports a formula parse error, check the parentheses, quotation marks, commas, and equals sign. Google Sheets normally uses commas between arguments in the examples here, but the spreadsheet’s locale can require semicolons instead. If a valid formula using commas produces a parse error, check File > Settings for the spreadsheet locale and use the argument separator expected by that locale. Do not change separators at random if the formula already parses.

A formula may also be correct but refer to a range that contains no matching data. Try selecting the referenced cells and using the browser’s find feature to locate a sample value, then compare the actual cell content with the criterion. If the result changes when you temporarily use a simple known value, the issue is likely in the original criterion or data rather than in COUNTIF itself.

When COUNTIFS or another approach is better

COUNTIF handles one condition. COUNTIFS is the appropriate function when every condition must be true, such as counting records with a particular status in one column and a region in another. Its criteria ranges should have matching dimensions so each row’s values are evaluated together.

With statuses in C2:C6 and regions in D2:D6, where two rows have Open in C and West in D, use =COUNTIFS(C2:C6,"Open",D2:D6,"West"). The result is 2. If you need one condition or another—for example, Open or Pending—add two COUNTIF results, or use a different formula suited to the data. Be careful not to add overlapping conditions if a row could be counted twice.

If you need to count only visible rows after applying a filter, ordinary COUNTIF is not a filter-aware count: it still evaluates matching cells in hidden rows. If you need counts based on color, COUNTIF also cannot use a fill color as its criterion. In those cases, use a helper column that records the property you want to test, or choose a method designed for the specific requirement. A helper column is often easier to audit than a complicated formula.

A quick troubleshooting sequence

Work through these checks in order so you can identify the source rather than masking it with a broader criterion:

Confirm the formula starts with = and is not being displayed as plain text.
Verify the range includes all relevant rows and the correct column.
Test the criterion against one known matching cell, including its spaces and punctuation.
Check whether values are numbers, dates, or text, rather than relying only on how they are formatted.
Use wildcards only when partial matching is intended; remember that they can include extra values.
For multiple conditions, switch to COUNTIFS and align the criteria ranges.
If the result is still wrong, make a small copy of the data and test a simpler formula there.

These steps also help distinguish a formula error from a data-quality issue. A formula that returns zero is not necessarily broken: it may be accurately reporting that no stored values satisfy the condition. Before widening the criterion, decide whether the extra matches would be valid for the question you are asking.

Frequently Asked Questions

Why does COUNTIF return 0 when the text looks the same?

The cells may contain leading or trailing spaces, a nonbreaking space, a line break, or another hidden character. Check the formula bar, retype a clean sample, and use a helper column with TRIM for ordinary spaces. Also confirm the formula is checking the right range.

Is Google Sheets COUNTIF case-sensitive?

No. COUNTIF does not distinguish uppercase from lowercase in text criteria, so “Open” and “open” are treated as matches. If capitalization itself must determine whether a value counts, COUNTIF alone is not the right tool.

Can COUNTIF count cells that contain a word?

Yes. Use an asterisk before and after the word in the criterion to match text containing it, such as "*apple*". This can also match longer words or phrases that include those letters, so review whether partial matches are appropriate.

Why is the COUNTIF formula showing as text?

The cell may be formatted as plain text, or the formula may have been entered with a leading apostrophe. Change the format through Format > Number > Automatic, edit the cell, and press Enter to make Sheets evaluate it.

Does COUNTIF ignore filtered or hidden rows?

No. COUNTIF counts matching cells in its range even when their rows are hidden by a filter. If you need a count that responds to visible rows only, use a filter-aware approach rather than assuming COUNTIF will omit hidden records.

Should I use COUNTIF or COUNTIFS?

Use COUNTIF for one condition, such as counting a status. Use COUNTIFS when the same row must meet two or more conditions, such as a status and a region. Keep the criteria ranges aligned so each set of conditions applies to the intended record.

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.