Data Validation & Audit
Verify Value Does Not Exist in a Blocklist (COUNTIF = 0 Anti-Join)
Enforce "must not exist in list" blocklist rules on vendor IDs, reserved usernames, or sanctioned codes using COUNTIF($E$2:$E$6, A2) = 0 and audit batch blocklist hits.
Preventing reserved usernames, retired SKUs, and sanctioned vendors from entering clean tables!
Onboarding forms, SKU generators, and accounts-payable ledgers frequently maintain a restricted blocklist—such as reserved system handles ("admin", "root", "support"), discontinued part numbers, or OFAC-restricted vendor codes. Testing COUNTIF($D$2:$D$5, A2) = 0 returns TRUE (or "Approved") ONLY when zero exact matches exist in the blocklist (trimming whitespace first with TRIM(A2) so accidental trailing spaces cannot sneak a blocked ID past validation)!
Across an entire submission column A2:A6, summing per-row blocklist flags with SUMPRODUCT(--(COUNTIF($D$2:$D$5, A2:A6) = 0)) or checking a helper column instantly counts how many clean non-blocked records are ready for import!
| Submitted Handle (A) | Blocklist Match COUNTIF($D$2:$D$5, TRIM(A)) | Gate Status | Audit Interpretation |
|---|---|---|---|
| "nova_analytics" | 0 matches | "Approved" | Safe to Provision |
| "admin" (Case-Insensitive) | 1 match ("ADMIN") | "Blocked — Reserved ID" | COUNTIF Catches Any Case |
| "root " (Trailing Space) | 1 match after TRIM() | "Blocked — Reserved ID" | Prevents Space-Padding Bypass |
Single-Entry Space-Safe Blocklist Gate (Must Not Exist in List)
Strips leading/trailing spaces with TRIM(A2) and checks that zero matches exist inside blocklist range $D$2:$D$5.
=IF(LEN(TRIM(A2)) = 0, "Blank Entry", IF(COUNTIF($D$2:$D$5, TRIM(A2)) = 0, "Approved", "Blocked — Reserved ID"))Count Blocked Submissions Across Candidate Batch
Counts how many rows in helper status column B2:B6 triggered a blocklist hit (> 0 matches).
=COUNTIF(B2:B6, ">0")Try the example
Use the original values or change them to test your understanding.