Google Sheets COUNTIF Not Equal to Text

Yes. In Google Sheets, use =COUNTIF(A2:A20,"<>Apple") to count cells in A2:A20 that are not equal to the text Apple. If you also need to exclude empty cells, use COUNTIFS with a second condition for nonblank cells.

How the not-equal criterion works

COUNTIF has two arguments: the range to examine and the criterion each cell must satisfy. In the criterion, the operator <> means “not equal to.” Put the operator and the text together inside quotation marks. Google Sheets compares each cell with the specified text and counts the cells that do not match it.

For a concrete example, suppose A2:A8 contains Apple, Pear, Apple, Banana, an empty cell, pear, and Green Apple, in that order. With those values in that range, this formula counts five cells:

With the example values in A2:A8, use:

=COUNTIF(A2:A8,"<>Apple")

The five counted cells are Pear, Banana, the empty cell, pear, and Green Apple. The two cells containing Apple are not counted. The blank result is worth noting: an empty cell is not equal to Apple, so this condition can include blanks. If blanks should not be part of your answer, add a nonblank condition as described below.

The comparison is for the whole cell value, not whether the word appears somewhere inside the cell. For example, Green Apple is different from Apple, so it is counted. COUNTIF text matching is case-insensitive, so Apple and APPLE are treated as equal for this comparison.

Exclude blank cells as well

A frequent source of unexpected totals is counting blank cells along with the nonmatching text. COUNTIFS can require the same cell to meet two conditions: it must not equal Apple, and it must not be blank. The conditions are combined with AND logic, so both must be true for a cell to count.

Using the same A2:A8 example, the formula below returns 4. It counts Pear, Banana, pear, and Green Apple, while excluding both Apple entries and the empty cell.

With the example values in A2:A8, use:

=COUNTIFS(A2:A8,"<>Apple",A2:A8,"<>")

The second criterion, <> with no value after it, means “not empty.” This is a useful pattern for lists where unused rows are included in the selected range. It also avoids changing the range every time new data is added, provided the extra rows are genuinely empty.

A cell containing a space is not truly empty. If a source has spaces or other invisible characters, the nonblank condition may count those cells. In that situation, clean the source data or use a helper column to standardize it before counting.

Use a cell reference for the text to exclude

You do not have to type the comparison text into the formula. If D1 contains the item to exclude, join the not-equal operator to that cell reference with the ampersand. This makes a count easier to reuse: changing D1 changes the excluded text without editing the formula.

With values to check in A2:A20 and the excluded text in D1, use:

=COUNTIF(A2:A20,"<>"&D1)

If D1 contains Apple, the formula counts cells that do not match Apple. As with the direct-text version, the result can include blanks. To exclude blanks too, add a second condition:

With values in A2:A20 and the excluded text in D1, use:

=COUNTIFS(A2:A20,"<>"&D1,A2:A20,"<>")

This reference-based approach is convenient for a report with a dropdown or a changing filter value. If the comparison cell is empty, however, the criterion is no longer a comparison against a named item. Check that D1 contains the intended value before relying on the result.

Count other text, not numbers

“Not equal to Apple” does not automatically mean “other text.” A COUNTIF condition can count numeric values too, because a number is also not equal to Apple. If a mixed column contains text, numbers, and blanks, decide whether your question is about every nonmatching cell or only nonmatching text.

For example, COUNTIFS can add a wildcard condition to restrict the result to text values. With a text-only list in A2:A20, the following counts nonblank text that is not Apple:

With mixed values in A2:A20, use:

=COUNTIFS(A2:A20,"<>Apple",A2:A20,"<>",A2:A20,"*")

The * criterion is a wildcard that matches text strings. The nonblank condition remains useful because it makes the intent clear and prevents empty cells from being treated as candidates. Test the formula against a few known rows if your source includes unusual values, such as formulas returning empty strings.

Exact matches, partial matches, and wildcards

COUNTIF does not treat every criterion as a literal string in every circumstance. The asterisk wildcard represents any sequence of characters, while the question mark represents one character. As a result, a criterion containing those characters may match a pattern rather than only cells containing those literal symbols.

The basic criterion <>Apple excludes the exact text Apple; it does not exclude Green Apple or Apple Juice. If the intention is to exclude every value that starts with Apple, add a wildcard after the word, as in <>Apple*. That changes the question from “not exactly Apple” to “does not begin with Apple,” so choose the criterion that matches the task.

To search for an actual asterisk or question mark in text, precede the wildcard character with a tilde in the criterion. This escape behavior matters for product codes, filenames, or labels that use those symbols literally. When a criterion is assembled from a cell reference, check whether the reference contains wildcard characters; they may affect the match rather than behave as ordinary punctuation.

Common reasons the count looks wrong

If a result surprises you, check the range and the values before changing the formula. A correct formula applied to the wrong rows, or to data with hidden differences, can still produce a misleading total.

Blanks are included: Add the second COUNTIFS condition <> if empty cells should not count.
Spaces or hidden characters: A value such as “Apple ” with a trailing space may not match Apple. Clean or standardize the source when exact text consistency matters.
Case expectations: COUNTIF does not distinguish uppercase and lowercase text. If capitalization is meaningful, this function may not answer the question as intended.
Wrong kind of match: Decide whether you mean an exact different value, a value that does not begin with a phrase, or text that does not contain a phrase. Wildcards change the criterion.
Mixed data: A not-equal condition can include numbers and other nontext entries. Add a text condition if the count should be limited to text.
Range size: Confirm that the range starts and ends on the rows containing the records you mean to count. A range that is too large may include unrelated data.

For a quick check, filter or inspect a few rows that should count and a few that should not. Compare the displayed cell contents carefully, especially when values were pasted from websites or exported from another system.

COUNTIF compared with COUNTIFS

Use COUNTIF when one condition is enough: count cells that are not a particular text value. Use COUNTIFS when the result needs multiple conditions, such as “not Apple and not blank” or “not Apple and text only.” COUNTIFS applies all supplied conditions to corresponding cells in the ranges.

In Google Sheets, both functions are available directly in a spreadsheet formula; there is no special setting to enable. The basic formula syntax for this comparison is also familiar to Excel users, though this page describes Google Sheets in a browser. If a workbook is shared between Sheets and Excel, verify the result after conversion when criteria, imported data, or wildcard characters are involved.

For a single exclusion, COUNTIF is usually easiest to read. Do not add conditions just to make a formula longer; use COUNTIFS when each added condition expresses a real requirement. A clear formula is easier to audit when someone else reviews the sheet or when the source range changes.

Frequently Asked Questions

Does COUNTIF not equal text count blank cells?

It can. An empty cell is not equal to the specified text, so a criterion such as <>Apple may include it. Add a second COUNTIFS criterion, <>, when the count must exclude blanks.

Is COUNTIF case-sensitive in Google Sheets?

No. COUNTIF treats uppercase and lowercase versions of text as equivalent. Thus, a condition excluding Apple also excludes APPLE for the comparison. If capitalization must distinguish values, choose a case-sensitive method instead of relying on COUNTIF.

How do I count cells that are not equal to the value in another cell?

Join the operator to the reference with an ampersand. If the values are in A2:A20 and the text to exclude is in D1, use =COUNTIF(A2:A20,"<>"&D1). Add a nonblank condition if empty cells should not count.

Does not equal mean the text is not contained anywhere in the cell?

No. A plain not-equal criterion compares the cell value with the specified text; it does not test whether the text appears as part of a longer value. For example, Green Apple is not equal to Apple. Use a wildcard criterion when you intend a pattern-based match.

Can COUNTIF count only text that is not a specific word?

Yes. Combine the not-equal criterion with a nonblank criterion and the text wildcard * in COUNTIFS. This distinguishes nonmatching text from numbers and empty cells in a mixed range. Check the source values if formulas or imported data create unusual blank-looking entries.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.