5 Ways To Use Does Not Contain In Google Sheets

The power of Google Sheets! With its robust features and versatile functionality, it's no wonder why millions of users rely on it for their data analysis and management needs. One of the most useful functions in Google Sheets is the "Does Not Contain" feature, which allows you to filter and manipulate data with ease. In this article, we'll explore five ways to use "Does Not Contain" in Google Sheets, and provide you with practical examples and tips to enhance your spreadsheet skills.

What is "Does Not Contain" in Google Sheets?

Before we dive into the five ways to use "Does Not Contain" in Google Sheets, let's quickly define what this feature does. "Does Not Contain" is a logical operator that allows you to filter data that does not contain a specific value or text string. It's often used in combination with other functions, such as FILTER, QUERY, and COUNTIFS, to analyze and manipulate data.

1. Using "Does Not Contain" with FILTER Function

One of the most common ways to use "Does Not Contain" in Google Sheets is with the FILTER function. This allows you to filter a dataset and return only the rows that do not contain a specific value or text string. Here's an example:

Suppose you have a dataset of customer names and email addresses, and you want to filter out all the rows that contain the text "gmail.com". You can use the following formula:

=FILTER(A:B, NOT(REGEXMATCH(B:B, "gmail.com")))

This formula will return all the rows that do not contain "gmail.com" in the email address column.

Example:

Customer Name	Email Address
John Doe	johndoe@gmail.com
Jane Doe	janedoe@yahoo.com
Bob Smith	bobsmith@hotmail.com

After applying the FILTER formula, the resulting dataset will be:

Customer Name	Email Address
Jane Doe	janedoe@yahoo.com
Bob Smith	bobsmith@hotmail.com

2. Using "Does Not Contain" with QUERY Function

Another way to use "Does Not Contain" in Google Sheets is with the QUERY function. This allows you to run a SQL-like query on your data and return only the rows that meet specific conditions. Here's an example:

Suppose you have a dataset of sales data, and you want to return only the rows that do not contain the text "Discount" in the Order Description column. You can use the following formula:

=QUERY(A:D, "SELECT * WHERE D <> 'Discount'")

This formula will return all the rows that do not contain "Discount" in the Order Description column.

Example:

Order ID	Order Date	Order Description	Total
1	2022-01-01	Discounted Order	100
2	2022-01-02	Regular Order	200
3	2022-01-03	Discounted Order	300

After applying the QUERY formula, the resulting dataset will be:

Order ID	Order Date	Order Description	Total
2	2022-01-02	Regular Order	200

3. Using "Does Not Contain" with COUNTIFS Function

The COUNTIFS function is another powerful tool in Google Sheets that allows you to count the number of cells that meet multiple conditions. You can use "Does Not Contain" with COUNTIFS to count the number of cells that do not contain a specific value or text string. Here's an example:

Suppose you have a dataset of student grades, and you want to count the number of students who do not have a grade of "F" in the Math column. You can use the following formula:

=COUNTIFS(B:B, "<>F")

This formula will count the number of cells in the Math column that do not contain "F".

Example:

Student Name	Math	Science	English
John Doe	A	B	C
Jane Doe	F	C	B
Bob Smith	A	B	A

After applying the COUNTIFS formula, the result will be 2, indicating that two students do not have a grade of "F" in the Math column.

4. Using "Does Not Contain" with REGEXMATCH Function

The REGEXMATCH function is a powerful tool in Google Sheets that allows you to search for patterns in text strings using regular expressions. You can use "Does Not Contain" with REGEXMATCH to search for text strings that do not contain a specific pattern. Here's an example:

Suppose you have a dataset of email addresses, and you want to find all the addresses that do not contain the text "edu". You can use the following formula:

=NOT(REGEXMATCH(A:A, "edu"))

This formula will return TRUE for all the email addresses that do not contain "edu".

Example:

Email Address
johndoe@gmail.com
janedoe@yahoo.edu
bobsmith@hotmail.com

After applying the REGEXMATCH formula, the resulting dataset will be:

Email Address
johndoe@gmail.com
bobsmith@hotmail.com

5. Using "Does Not Contain" with ARRAYFORMULA Function

The ARRAYFORMULA function is a powerful tool in Google Sheets that allows you to perform calculations on entire arrays of data. You can use "Does Not Contain" with ARRAYFORMULA to perform calculations on arrays that do not contain a specific value or text string. Here's an example:

Suppose you have a dataset of sales data, and you want to calculate the total sales for all the orders that do not contain the text "Discount" in the Order Description column. You can use the following formula:

=ARRAYFORMULA(SUMIFS(D:D, NOT(REGEXMATCH(C:C, "Discount"))))

This formula will calculate the total sales for all the orders that do not contain "Discount" in the Order Description column.

Example:

Order ID	Order Date	Order Description	Total
1	2022-01-01	Discounted Order	100
2	2022-01-02	Regular Order	200
3	2022-01-03	Discounted Order	300

After applying the ARRAYFORMULA formula, the result will be 200, indicating the total sales for all the orders that do not contain "Discount" in the Order Description column.

Does Not Contain Image Gallery

We hope this article has helped you learn more about using "Does Not Contain" in Google Sheets. With these five examples, you can now apply this powerful feature to your own datasets and improve your data analysis skills. Remember to experiment with different functions and formulas to get the most out of "Does Not Contain" in Google Sheets.

What's your favorite way to use "Does Not Contain" in Google Sheets? Share your tips and tricks in the comments below!