Ishrak Khan
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.
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)
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.
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.
Steps:
- We will select cell C13 and use the formula.
=ARRAYFORMULA(SUM(COUNTIF(B5:B9,{"Orange*","*Shake"})))- Finally, press ENTER to get the result.
- 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 Use COUNTIF Function with OR Logic in Google Sheets
- COUNTIF with Greater than and Less than Criteria in Google Sheets
- How to Use VLOOKUP with COUNTIF Function in Google Sheets
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.
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.
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“.
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.
