Text & Cleaning
Test Whether a Range Contains Any Substring from a Keyword List (COUNTIF)
Check whether any cell across an entire column or log range contains at least one keyword from a watchlist using wildcard COUNTIF checks, returning a clean TRUE/FALSE flag or total keyword hits.
Scan an entire batch of log lines against multiple alert substrings
Testing whether a single cell contains a substring uses ISNUMBER(SEARCH(term, cell)), whereas testing whether any cell in a multi-row range B2:B6 contains any substring from a keyword table D2:D4 requires scanning the Cartesian product of rows and keywords.
Evaluating COUNTIF(B2:B6, "*" & D2 & "*") + COUNTIF(B2:B6, "*" & D3 & "*") + COUNTIF(B2:B6, "*" & D4 & "*") > 0 checks each non-blank keyword across the entire log range without helper columns. Guarding empty watchlist cells with IF(LEN(TRIM(Dk)) = 0, 0, COUNTIF(...)) prevents a blank watchlist slot from expanding into "**" and matching every text cell.
| Incident Log Entry (B2:B6) | Watchlist Keyword (D2:D4) | Range Hit Count | Alert Triggered? |
|---|---|---|---|
| 2026-05-01 Auth token refreshed | timeout | 1 (Row 4) | TRUE |
| 2026-05-01 Cache warmup complete | deadlock | 0 | — |
| 2026-05-01 Gateway timeout on node-3 | corrupt | 0 | — |
| 2026-05-01 Nightly backup verified | (Total hits = 1) | 1 > 0 | Alert Active |
Blank-Safe Multi-Keyword Substring Scan Across a Range
Tests whether any cell in B2:B6 contains at least one non-empty substring listed in D2:D4.
=LET(h1, IF(LEN(TRIM(D2)) = 0, 0, COUNTIF(B2:B6, "*" & TRIM(D2) & "*")), h2, IF(LEN(TRIM(D3)) = 0, 0, COUNTIF(B2:B6, "*" & TRIM(D3) & "*")), h3, IF(LEN(TRIM(D4)) = 0, 0, COUNTIF(B2:B6, "*" & TRIM(D4) & "*")), (h1 + h2 + h3) > 0)Total Keyword-Row Hit Count Across the Range
Returns the total number of keyword-cell matches found across B2:B6 for severity triage.
=IF(LEN(TRIM(D2)) = 0, 0, COUNTIF(B2:B6, "*" & TRIM(D2) & "*")) + IF(LEN(TRIM(D3)) = 0, 0, COUNTIF(B2:B6, "*" & TRIM(D3) & "*")) + IF(LEN(TRIM(D4)) = 0, 0, COUNTIF(B2:B6, "*" & TRIM(D4) & "*"))Try the example
Use the original values or change them to test your understanding.