Conditional Aggregation
Count Non-Blank Cells That Do Not Contain Any Blocklisted Substrings
Count clean transaction or SKU rows that exclude multiple unwanted substrings simultaneously using multi-criteria COUNTIFS wildcard exclusions and row-level SEARCH flags.
Chain AND-combined wildcard exclusions without overlap bugs
Subtracting individual COUNTIF(range, "*tag*") counts from COUNTA(range) fails whenever a single noisy row contains two excluded tags at once (for example, "TEST-VOID-99"), subtracting that row twice and understating the clean count!
Passing each excluded substring as an independent "<>*" & tag & "*" criterion inside COUNTIFS(range, "<>", range, "<>*" & tag1 & "*", range, "<>*" & tag2 & "*", range, "<>*" & tag3 & "*") enforces strict logical AND across every row while ignoring blank cells.
| Order Code | Contains TEST? | Contains VOID? | Contains DEMO? | Clean Production Row? |
|---|---|---|---|---|
| ORD-1041-LIVE | No | No | No | Yes (1) |
| ORD-TEST-009 | Yes | No | No | Excluded |
| ORD-1042-VIP | No | No | No | Yes (2) |
| TEST-VOID-88 | Yes | Yes | No | Excluded once |
| DEMO-SAMPLE-1 | No | No | Yes | Excluded |
| ORD-1043-STD | No | No | No | Yes (3) |
Clean Row Count Excluding Three Blocklisted Substrings
Counts non-blank cells in A2:A7 that contain none of the substrings in B9, B10, or B11.
=IF(OR(ISBLANK(B9), ISBLANK(B10), ISBLANK(B11)), "Populate All Blocklist Terms", COUNTIFS(A2:A7, "<>", A2:A7, "<>*" & B9 & "*", A2:A7, "<>*" & B10 & "*", A2:A7, "<>*" & B11 & "*"))Row-Level Clean vs Excluded Audit Tag (Row A5)
Flags whether row A5 contains any blocklisted substring so analysts can filter noisy staging records.
=IF(OR(ISNUMBER(SEARCH(B9, A5)), ISNUMBER(SEARCH(B10, A5)), ISNUMBER(SEARCH(B11, A5))), "Excluded", "Clean")Try the example
Use the original values or change them to test your understanding.