The first thing most people do when they need to flag cells that do not contain a specific text string is wrap COUNTIF around a wildcard like "*text*" and flip the logic with NOT. That fails silently on empty cells, breaks on #VALUE! errors, and gives false positives when the search term appears as part of another word. Don’t do it.
The Setup
You’re auditing supplier invoices in Column A (A2:A11). Each entry is a free-text description like "Freight charge – Acme Corp", "Discount applied – Beta Logistics", or "Late fee – Delta Solutions". You need to isolate rows where the description does not contain the word "discount" — case-insensitive, anywhere in the string.
| A2:A11 (Description) | B2:B11 (Expected Result) |
|---|---|
| Freight charge – Acme Corp | TRUE |
| Discount applied – Beta Logistics | FALSE |
| Refund processed – Gamma Inc | TRUE |
| DISCOUNT_OVERRIDE – Theta Ltd | FALSE |
| Service fee – Iota Systems | TRUE |
| discounted rate – Kappa Group | FALSE |
| — blank — | TRUE |
| Discount refund – Lambda Co | FALSE |
| Shipping label – Mu Enterprises | TRUE |
| Discount code used – Nu Tech | FALSE |
The Challenge
You can’t use COUNTIF("*discount*") here — it returns 0 for non-matches, but also 0 for blanks and errors. And =NOT(ISNUMBER(SEARCH("discount",A2))) seems right… until you paste it down and see #VALUE! in row 7 because SEARCH fails on an empty cell. That breaks filtering, sorting, and downstream formulas.
The core problem isn’t syntax — it’s error resilience. Excel treats "" (empty) and #N/A and #VALUE! all differently in logical tests. You need one expression that returns TRUE for blanks, FALSE only when "discount" appears — and survives copy-paste without breaking.
Walking Through It
Do this in cell C2. Then drag down to C11.
Step 1: Type =ISERROR(SEARCH("discount",A2)). Press Enter.
Result: #VALUE! in C7 (because A7 is blank), FALSE in C2, C4, C6, C8, C10, TRUE elsewhere. Not usable.
| A2:A11 | C2:C11 (Step 1) |
|---|---|
| Freight charge – Acme Corp | TRUE |
| Discount applied – Beta Logistics | FALSE |
| Refund processed – Gamma Inc | TRUE |
| DISCOUNT_OVERRIDE – Theta Ltd | FALSE |
| Service fee – Iota Systems | TRUE |
| discounted rate – Kappa Group | FALSE |
| — blank — | #VALUE! |
| Discount refund – Lambda Co | FALSE |
| Shipping label – Mu Enterprises | TRUE |
| Discount code used – Nu Tech | FALSE |
Step 2: Fix the error by wrapping with IFERROR. In C2, replace the formula with:=IFERROR(ISERROR(SEARCH("discount",A2)),TRUE)
Press Ctrl+Enter to keep the cursor in C2 after typing — faster than Enter when building formulas.
This tells Excel: “If SEARCH throws any error (#VALUE!, #N/A, etc.), return TRUE. Otherwise, return ISERROR’s result.” Empty cells now safely return TRUE — exactly what we want for “does not contain”.
| A2:A11 | C2:C11 (Step 2) |
|---|---|
| Freight charge – Acme Corp | TRUE |
| Discount applied – Beta Logistics | FALSE |
| Refund processed – Gamma Inc | TRUE |
| DISCOUNT_OVERRIDE – Theta Ltd | FALSE |
| Service fee – Iota Systems | TRUE |
| discounted rate – Kappa Group | FALSE |
| — blank — | TRUE |
| Discount refund – Lambda Co | FALSE |
| Shipping label – Mu Enterprises | TRUE |
| Discount code used – Nu Tech | FALSE |
Step 3 (optional but recommended): Make it case-insensitive *and* ignore extra spaces. Replace "discount" with TRIM(SUBSTITUTE(LOWER(A2)," ",""))? No — overkill. Instead, use LOWER inside SEARCH: =IFERROR(ISERROR(SEARCH("discount",LOWER(A2))),TRUE). Now "DISCOUNT_OVERRIDE" and "discounted" both trigger FALSE. Do this in D2. Drag down.
The Result
Column D is your clean, production-ready "does not contain" flag. Use it to filter, conditional format, or feed into SUMIFS. Here’s the final output:
| A2:A11 | D2:D11 (Final) |
|---|---|
| Freight charge – Acme Corp | TRUE |
| Discount applied – Beta Logistics | FALSE |
| Refund processed – Gamma Inc | TRUE |
| DISCOUNT_OVERRIDE – Theta Ltd | FALSE |
| Service fee – Iota Systems | TRUE |
| discounted rate – Kappa Group | FALSE |
| — blank — | TRUE |
| Discount refund – Lambda Co | FALSE |
| Shipping label – Mu Enterprises | TRUE |
| Discount code used – Nu Tech | FALSE |
What Could Go Wrong
Here are the three mistakes I see in live training — every time.
| Symptom | Cause | Fix |
|---|---|---|
| Formula returns FALSE for blank cells | Used =NOT(ISNUMBER(SEARCH(...))) without IFERROR | Wrap entire SEARCH expression in IFERROR(...,TRUE) |
| "Discount" missed in "DISCOUNTED" | Search term was "Discount" (capital D), but data has mixed case | Always wrap lookup cell in LOWER(), e.g., SEARCH("discount",LOWER(A2)) |
| Filter shows no rows when filtering for TRUE | Applied filter to column D before dragging formula down to D11 — so D7:D11 are blank, not TRUE/FALSE | Select D2:D11 first, then press Ctrl+D (Fill Down) instead of dragging |
One counterintuitive tip: Never use COUNTIF or COUNTIFS for “does not contain”. Those functions count matches — they don’t evaluate per-cell presence. You’ll spend 20 minutes debugging why =COUNTIF(A2:A11,"*discount*")=0 returns TRUE for a range with 1 discount and 9 blanks. Just don’t go there.
Your next step: Open your workbook. Go to the sheet with text data. In an empty column beside your text, type =IFERROR(ISERROR(SEARCH("your_text_here",LOWER(A2))),TRUE) — replacing "your_text_here" and A2 with your actual values. Press Ctrl+D to fill down. Then filter on that column for TRUE.
