ExcelInsider Team
Google Sheets makes it easy to count cells that include a specific word or phrase fragment using the COUNTIF function. This is especially helpful when tracking statuses like “Shipped – Express” or “Pending Approval,” where the keyword is only part of the cell content.
In this article, we’ll explore simple ways to use COUNTIF with partial text matches using a sample dataset of order statuses.
Steps to count cells that contain a specific word using COUNTIF:
➤ Click on a blank cell where you want the result to appear, such as E2.
➤ Type the following formula: =COUNTIF(C2:C11, “*Shipped*”)
➤ The asterisks * are wildcards, they allow any characters before or after “Shipped”.
➤ Press Enter.
Counting Cells Containing a Specific Word Using COUNTIF Function
If your dataset includes status labels like “Shipped – East”, “Pending Approval”, or “Partially Shipped”, and you want to count how many contain a specific keyword, the COUNTIF function with wildcards is perfect.
We’ll use the sample dataset where column C contains order statuses, and count how many include the word “Shipped”:
Steps:
➤ Click on a blank cell (e.g., E2) where you want the result to appear.
➤ Enter the following formula:
=COUNTIF(C2:C11, “*Shipped*”)
➤ Press Enter.
The formula will return the number of cells in C2:C11 that include “Shipped” anywhere in the text.
Count Cells That Include Text from Another Cell in Google Sheets
In Google Sheets, there are times when you want to make your search flexible. For instance, allowing a user to enter a keyword like “Pending” or “Shipped” into a cell and dynamically count how often it appears in a list. In this method, we’ll use the same dataset where column C contains order statuses and look for matches based on a keyword typed into cell D1.
Steps:
➤ In cell D2, type the word you want to count (e.g., Pending).
➤ Click on a blank cell where you want the result to appear (e.g., E2).
➤ Enter the following formula:
=COUNTIF(C2:C11, “*” & D1 & “*”)
➤ Press Enter.
The result will show how many times the keyword in D1 appears in column C. Update the keyword in D1 to get real-time counts for different terms.
Frequently Asked Questions
How do I count cells that contain a specific word in Google Sheets?
Use =COUNTIF(range, “*word*”) to count cells containing a specific word anywhere in the text. The asterisks act as wildcards before and after the word.
Can COUNTIF match partial text in Google Sheets?
Yes, COUNTIF can match partial text using wildcards. Use * before and after your keyword in the criteria to match any part of the cell content.
What if my search keyword is in a cell?
Use =COUNTIF(range, “*” & cell & “*”) to dynamically count cells containing the keyword from another cell. It updates automatically if the keyword changes.
Is COUNTIF case-sensitive when searching for text?
No, COUNTIF is not case-sensitive. It treats “shipped”, “Shipped”, and “SHIPPED” as the same. For case-sensitive counts, use a custom formula with REGEXMATCH and ARRAYFORMULA.
Can I count multiple keywords with COUNTIF?
COUNTIF handles one condition. To count multiple keywords, use multiple COUNTIF formulas added together, or switch to COUNTIFS or FILTER with ARRAYFORMULA for advanced logic.
Wrapping Up
Using COUNTIF with partial text in Google Sheets is a simple yet powerful way to analyze data based on keywords or fragments. Whether you’re tracking order statuses, filtering descriptions, or monitoring notes, wildcards help you capture variations without extra effort. With just a few formulas, you can turn raw lists into meaningful insights, all without manual scanning or filtering.
As your datasets grow, mastering partial text search with COUNTIF will save time and make your sheets far more dynamic.
