I need help counting cells in Google Sheets that contain a particular word when the cells have other text too.
I want to check a range of cells (like B1:B50) and count how many contain the word TARGET even when there are other words or numbers in the same cell.
I tried this formula:
=COUNTIF(B1:B50, "TARGET")
But it only works when the cell contains exactly TARGET and nothing else. When the cell has something like “The TARGET value is 100”, it doesn’t count it.
I also attempted using regex functions:
=COUNTIF(B1:B50, REGEXMATCH(B1:B50, "TARGET"))
But this approach returns zero matches as well. What’s the correct way to count cells that contain my target word as part of a longer text string?
Try =COUNTIFS(B1:B50,"*TARGET*") instead - I find it more reliable with complex datasets. COUNTIF gets weird sometimes, especially when cells have line breaks or special characters from copy/paste. COUNTIFS handles those edge cases better. Also check for leading/trailing spaces in your cells - this bit me once with imported data that had hidden whitespace breaking the matches. You can use TRIM if needed: =COUNTIF(B1:B50,"*TARGET*") works too, but cleaning the data first saves headaches.
hey elizabeths! you need wildcards - try =COUNTIF(B1:B50, "*TARGET*"). the asterisks match anything before and after your word. perfect for partial matches like yours!
The wildcard approach is definitely the way to go. I hit this exact issue analyzing survey responses - participants wrote long comments but I needed to count specific keyword mentions. The *TARGET* syntax works perfectly here. Just watch your capitalization since Google Sheets is case-sensitive by default. For case-insensitive matching, try =COUNTIF(B1:B50, "*target*") after converting your range to lowercase, or use =SUMPRODUCT(--(ISNUMBER(SEARCH("TARGET",UPPER(B1:B50))))) for trickier scenarios. SEARCH is great for mixed case data since it ignores case differences.