Use COUNTIF If Cell Contains Specific Text in Google Sheets

In business and finance, it’s essential to analyze data quickly and accurately. One way to do this is by using the COUNTIF function in Google Sheets to count cell that contains specific text.

With this function, you can easily determine the number of occurrences of a specific word or phrase within a range of cells, making it a powerful tool for data analysis and decision-making. For example, in a business or financial setting one may need to track the number of sales for a specific product or category.

2 Examples to Use COUNTIF If Cell Contains Specific Text in Google Sheets

The COUNTIF function in Google Sheets allows for counting cells that contain specific text, offering a variety of search criteria including exact matches and partial matches. There are several ways to use COUNTIF to achieve this.
Syntax:

`COUNTIF(range, criterion)`

1. Find Exact Match or Specific Text

We have a dataset of an inventory that contains information about invoice numbers, products, quantities, and total prices. To count how many times “Pen” was sold in our inventory dataset, we can use the COUNTIF function in Google Sheets. Check the following steps to do it.

Steps:

• Firstly, we will select the cell, C13.

• Secondly, we will use the following formula for a quick count.
`=COUNTIF(C5:C9,C12)`

• Press ENTER and the result will show how many times the item “Pen” was sold in the given range.

The result shows that Pen was sold 2 times, as seen in the image.

2. Count Two or More Texts at the Same Time

We can count multiple texts at once by using the ARRAYFORMULA and SUM functions on our dataset. Using this method, we can count cells that contain specific texts, like “Orange” at the beginning and “Shake” at the end.

Steps:

• We will select cell C13 and use the formula.
`=ARRAYFORMULA(SUM(COUNTIF(B5:B9,{"Orange*","*Shake"})))`
• Finally, press ENTER to get the result.

Formula Breakdown:

• The range for the COUNTIF function is B5:B9 and the criterion is an array of texts to be searched for, in this case {“Orange*”,”*Shake”}.
• The SUM function adds up the number of cells that match each criterion.
• To allow the use of multiple criteria in the COUNTIF function in an array or range, we used ARRAYFORMULA.

In this example, it will return an output of 3, as three cells contain these specific texts.

How to COUNTIF If Cell Does Not Contain Specific Text in Google Sheets

Using the COUNTIF function in Google Sheets, we can easily find out how many times a specific product, like Pen, was not sold in our inventory dataset. To do this check the following process.

Steps:

• Once again, we will select cell C13.
• Thereafter we will use the following formula.
`=COUNTIF(C5:C9,"<>Pen")`

• Finally, press ENTER to get the result.

It is evident that Pen was not sold in 3 out of 5 invoices as the result shows.

How to COUNTIF If Cell Contains Partial Text in Google Sheets

Businesses and marketers can benefit from the COUNTIF function in Google Sheets, which allows them to count cells that contain certain text.

The COUNTIF function, for instance, might be used to look for a partial match of the product name inside the cells if a business wishes to track how frequently a specific product name appears in a list of sales. This can assist companies in determining the effectiveness of their marketing initiatives and in making data-driven decisions.

I. Beginning of Text

One of the ways is to check how many times the word “Pen” comes in the first part of the cell. We will use the previous dataset to demonstrate the process.

Steps:

• We’ll use cell C14 once more.
• Later, we’ll apply the following formula.
`=COUNTIF(C6:C10,"Pen*")`
• Pressing ENTER will show the result of the formula.

Both Pen and Pencil have “Pen” in the name, so it returned a count of 4.

II. Middle of Text

With this method, we can find out how many times a specific text appears in the middle of a cell. Using the dataset provided, we’ll show an example of counting how many times the word “Heart” appears in the product list.

Steps:

• First, we will select cell C14 once again.
• We will use the following formula.
`=COUNTIF(B6:B10,"*Heart*")`
• In the end, press ENTER to see the result.

We can observe that the word “Heart” appears in two different texts.

III. End of Text

We can use the following dataset to count how many cells have a specific text at the end.

Steps:

• Initially, we will select cell C14.
• Then we will apply the following formula.
`=COUNTIF(B6:B10,"*Shake")`
• To get the result, we will press ENTER.

This will return a result of 2, showing that there are only two products, Chocolate Milkshake and Banana Milkshake, in the list that end with “Shake“.

Conclusion

By utilizing the COUNTIF function, businesses can make data-driven decisions and improve their overall efficiency and profitability. This function can be particularly useful in identifying key metrics such as customer demographics, sales figures, and budget expenses.

The COUNTIF function allows users to quickly and easily count the number of cells that contain specific text, providing valuable insights into trends, patterns, and performance. Check OfficeWheel for more relevant content.

Related Articles

Ishrak Khan

Ishrak Khan is a writer and urban planner born in Dhaka, Bangladesh, one of the largest mega-cities. He is a professional Content Developer closely working with OfficeWheel. He simply likes to enthrall his readers. He loves Football, Nico Robin, Deserts, and Twix. If you like the post, give him a heart on Instagram.

We will be happy to hear your thoughts