How to Count Cells That Contain Specific Text in Google Sheets?
Count Cells with Specific Text is a Google Sheets function that counts the number of cells in a specified range that contain a specific word or partial text.. Formula Genius generates and validates this formula automatically from a plain-English prompt.
Struggling to count occurrences of a word or phrase in your data? Use the COUNTIF function with wildcards to easily achieve this.
The Formula
"Count cells that contain a specific word or partial text in Google Sheets using COUNTIF with wildcards"
=COUNTIF(A1:A10, "*word*")This formula counts the number of cells in a specified range that contain a specific word or partial text.
Step-by-Step Breakdown
- A1:A10 is the range of cells you want to evaluate.
- The asterisks (*) are wildcards that represent any number of characters.
- "word" is the specific text you want to search for within the cells.
Edge Cases & Warnings
- The search term is case-sensitive if using EXACT instead of COUNTIF.
- Cells with leading or trailing spaces may not be counted unless trimmed.
- Using special characters in the search term may yield unexpected results.
Examples
"Range: A1:A5 contains 'apple', 'banana', 'apple pie', 'grape', 'pineapple'; search term: 'apple'"
3"Range: A1:A4 contains 'dog', 'cat', 'doghouse', 'fish'; search term: 'dog'"
2Frequently Asked Questions
Can I count cells that contain multiple words?
Yes, you can use COUNTIF with multiple criteria by combining functions.
What if my search term includes special characters?
You may need to escape special characters or use a different approach.
Is COUNTIF case-sensitive?
No, COUNTIF is not case-sensitive by default.
Can't find what you need?
Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.