ExcelInsider Team
When analyzing text data in Google Sheets, it’s often helpful to count how many cells contain a certain word or phrase. Whether you’re working with product names, tags, customer feedback, or any descriptive field, the COUNTIF function allows you to easily search for and count cells that match your criteria.
In this article, you’ll learn several ways to use COUNTIF to check if a cell contains specific text, whether it’s an exact match, partial match, prefix, or suffix. We’ll also show you how to ignore certain keywords and how wildcards can make your formulas more flexible, all with clear examples you can apply right away.
Steps to count cells that contain specific text (partial match) in Google Sheets using the COUNTIF function:
➤ Use the dataset in cells A1:C11, where columns represent Order ID, Customer Feedback, and Order Value.
➤ To count how many feedback entries contain the word “late”, enter this formula in cell E2:
=COUNTIF(A2:C11, “*late*”)
➤ Press Enter to return the count of feedback entries mentioning “late”.
Count Cells That Contain Specific Text (Partial Match)
This method uses the COUNTIF function to count cells that contain a specific word or phrase anywhere within the cell. It is helpful when you want to find how many entries mention a particular keyword, even if it appears as part of longer text. This is useful for analyzing comments, feedback, or notes in your data.
This is the dataset we will be using to demonstrate the methods:
Steps:
➤ Use the dataset in cells A1:C11, where columns represent Order ID, Customer Feedback, and Order Value.
➤ To count how many feedback entries contain the word “late”, enter this formula in cell E2:
=COUNTIF(A2:C11, “*late*”)
➧ "*late*": The asterisks are wildcards that match any number of characters before or after the word "late".
➧ The formula counts the number of cells that contain the word "late" anywhere in the text.
➤ Press Enter, and the result will show how many customers mentioned “late” in their feedback.
➤ This method is useful for finding specific keywords within large text fields.
Identify Text Entries That Begin With a Specific Word Using COUNTIF
If your dataset includes cells where text entries begin with consistent keywords, like tags, categories, or recurring comments, you can use COUNTIF to count how many start with a certain word. This helps in grouping and analyzing recurring patterns within a column.
Steps:
➤ Use the dataset in cells A1:C11, where columns represent Order ID, Customer Feedback, and Order Value.
➤ To count how many feedback entries start with the word “Quick”, enter the following formula in cell E2:
=COUNTIF(A2:C11, “Quick*”)
➧ Placing the asterisk at the end ("Quick*") tells Google Sheets to match any cell that starts with the word "Quick", and is followed by anything else (including nothing).
➧ The COUNTIF function evaluates this pattern and returns the number of cells that match the criteria.
➤ Press Enter, and the result will show how many cells in the range begin with “Quick”.
➤ This method helps analyze structured text feedback or keyword-based inputs.
Exclude Specific Words When Counting Text Entries Using COUNTIF
When working with datasets, you may want to count text entries but exclude certain placeholder words like “None” and “N/A”. This method helps you count only relevant entries across multiple columns while ignoring the unwanted values.
This is the updated dataset for this method:
Steps:
➤ Use the dataset in cells A1:C11, where columns represent Order ID, Customer Feedback, and Order Value.
➤ To count how many cells contain text, excluding the words “None” and “N/A”, enter this formula in cell E2:
=COUNTIFS(B2:B11, “<>None”, B2:B11, “<>N/A”)
➧ "<>None" and "<>N/A": These criteria ensure cells exactly matching "None" or "N/A" are excluded from the count.
➧ Blank cells and other text or numbers will be counted unless they exactly match "None" or "N/A".
➤ Press Enter, and the formula will return the number of cells containing values other than “None” and “N/A”.
➤ This method improves data accuracy by filtering out unwanted placeholder text while counting all other entries in multiple columns.
Frequently Asked Questions
How do I count only text values in Google Sheets using COUNTIF?
Use =COUNTIF(range, “*”) to count only cells containing text. This excludes blank cells and numbers but includes all text entries, even partial words or phrases.
Can COUNTIF ignore specific words when counting text?
COUNTIF alone can’t handle multiple exclusions, but using helper columns with IF and ISTEXT lets you filter out specific words like “None” or “N/A” effectively.
What does the asterisk (*) mean in a COUNTIF formula?
The asterisk is a wildcard that matches any sequence of characters. In =COUNTIF(range, “*”), it tells Google Sheets to count all non-blank text entries.
Does COUNTIF count numbers and formulas as text?
No, COUNTIF(range, “*”) only counts actual text. Cells with numbers, dates, or formula-generated values are excluded unless they return text strings as the output.
Wrapping Up
Counting text entries accurately in Google Sheets can help you clean up datasets, track responses, or highlight incomplete fields. The COUNTIF function makes this easy with flexible criteria, whether you want to count all non-blank text, focus on specific phrases, or exclude placeholder values like “None” and “N/A”. By using the methods outlined in this article, you can build smarter summaries and reports that reflect only the data that matters. Try combining these formulas with helper columns or ARRAYFORMULA for even more control as your dataset grows.
