How to Count the Number of Cells in an Excel Sheet that Do Not Contain Specific Text

In this article, we will discuss how to use Excel functions such as COUNTIF, SUMPRODUCT, SEARCH, FIND, and NOT to achieve this.

Understanding the problem

Before delving into how to solve the problem, let’s understand the issue. Suppose you have an Excel sheet with a list of names, and you want to know how many names do not contain the word “John.” How do you find out?

Using COUNTIF function

One way to solve this problem is to use the COUNTIF function. The COUNTIF function counts the number of cells in a range that meet a specific criterion. To count the number of cells that do not contain the word “John,” we can use the following formula:

=COUNTIF(range,"<>*John*")

Here, the “<>John” criterion means any cell that does not contain the word “John.” The asterisk (*) acts as a wildcard character, indicating any text before or after the word “John.”

Using SUMPRODUCT, SEARCH, and NOT functions

Another way to solve the problem is to use the SUMPRODUCT, SEARCH, and NOT functions. The SUMPRODUCT function multiplies arrays and then returns the sum of the products. The SEARCH function returns the starting position of a text string within another text string, and the NOT function returns the opposite of a logical value.

To count the number of cells that do not contain the word “John” using these functions, we can use the following formula:

=SUMPRODUCT(--NOT(ISNUMBER(SEARCH("John",range))))

Here, the SEARCH function searches for the word “John” in the specified range, and the ISNUMBER function returns TRUE if a number is found and FALSE if a number is not found. The NOT function converts the logical value to its opposite. The double negative (–) converts the logical value to a numeric value that can be used in the SUMPRODUCT function.

Using FIND and NOT functions

We can also use the FIND and NOT functions to count the number of cells that do not contain the word “John.” The FIND function is similar to the SEARCH function, but it is case-sensitive.

To count the number of cells that do not contain the word “John” using the FIND and NOT functions, we can use the following formula:

=SUMPRODUCT(--NOT(ISNUMBER(FIND("John",range))))

Example

Let’s consider an example to understand the problem and its solution better. Suppose we have an Excel sheet with a list of names in column A, as shown below:

We want to count the number of names that do not contain the word “John.” Using the COUNTIF function, we can use the following formula:

=COUNTIF(A2:A11,"<>*John*")

This formula returns 6, indicating that there are six names that do not contain the word “John.”

Using the SUMPRODUCT, SEARCH, and NOT functions, we can use the following formula:

=SUMPRODUCT(--NOT(ISNUMBER(SEARCH("John",A2:A11))))

This formula also returns 6, indicating that there are six names that do not contain the word “John.”

Using the FIND and NOT functions, we can use the following formula:

=SUMPRODUCT(--NOT(ISNUMBER(FIND("John",A2:A11))))

This formula also returns 6, indicating that there are six names that do not contain the word “John.”