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:

Name
John
Mike
Sarah
John Smith
Rachel
Johnny
Johnny Bravo
John Doe
David
Jonathan

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.”

Photo of author

Bibek Sapkota

I'm Bibek | Tech Enthusiast & Lifelong Learner. | Playing on the Web for the Past Few Years as an SEO Specialist and Full-Time Blogger. I'm constantly seeking out new opportunities to learn and grow, and I love sharing my knowledge with others. This is where I started this blog! Here, you will find me sharing comprehensive reviews, helpful guides, tips-tricks and ways to get the full benefits of evolving technology. On this blog, you can also explore Powerful Knowledge, Tips & Resources On Blogging, SEO and Passive income Opportunities.

Leave a Comment