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.

overview image of countif cell contains specific text in google sheets


A Sample of Practice Spreadsheet

You can copy our spreadsheet that we’ve used to prepare this article.


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)

syntax of countif function

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.

data for countif cell contains specific text in google sheets

Steps:

  • Firstly, we will select the cell, C13.

selecting cell in google sheets

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

countif formula in google sheets to count exact match

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

final output from countif cell contains specific text in google sheets

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

Read More: [Fixed!] COUNTIF Function Is Not Working in Google Sheets


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.

data for counting two or more texts at the same time

Steps:

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

final output for counting two or more texts

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.

Read More: Google Sheets Count Cells from Another Workbook with COUNTIF Function


Similar Readings


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.

data for countif cell does not contain specific text

Steps:

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

formula for counting not specific text

  • Finally, press ENTER to get the result.

final output of cells do not contain specific text

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

Read More: How to Use COUNTIF for Cells Not Equal to Text in Google Sheets


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.

data for counting partial text

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.

result for countif cell containts text at the begining

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.

data for countif cell containts text in the middle

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.

final output from countif text at the middle

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.

data for countif contains cells 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.

final formula for countif cell contains text at the end

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

Read More: COUNTIF Function with “Not Equal to” Criterion in Google Sheets


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

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

Leave a reply

OfficeWheel
Logo