Counting & Conditional Aggregation
Count Cells With Exact N Characters Using COUNTIF & REPT("?", N)
Count text codes, SKUs, ZIP codes, or IBANs that match an exact N-character length dynamically using COUNTIF(B2:B7, REPT("?", D2))—and audit mismatched lengths automatically.
Turn the single-character "?" wildcard into a dynamic N-length validator with REPT!
In Excel and Arte Sheets wildcard syntax, an asterisk (*) matches any number of characters whereas a question mark (?) matches EXACTLY one character! Instead of hardcoding "?????" for 5-character postal codes or "????????" for 8-character warehouse SKUs, feed target length N (cell D2) into REPT("?", D2) to generate the exact N-question-mark pattern dynamically: =COUNTIF(B2:B7, REPT("?", D2))!
Note one crucial spreadsheet nuance: COUNTIF wildcard patterns match TEXT entries only (pure numeric 12345 is not matched by "?????"). When auditing imported SKU/ZIP text columns (B2:B7), pairing COUNTIF(B2:B7, REPT("?", D2)) with COUNTA(B2:B7) - COUNTIF(B2:B7, REPT("?", D2)) immediately flags truncated or overlong text codes!
| Target Text Length D2 | Generated Wildcard REPT("?", D2) | Matching SKUs in B2:B7 | Count Returned |
|---|---|---|---|
| D2 = 6 Characters | "??????" | "WH-104", "WH-209", "AZ-881" | 3 Valid 6-Char Codes |
| D2 = 8 Characters | "????????" | "WH-1042A", "EU-9901B" | 2 Valid 8-Char Codes |
| D2 <= 0 (Invalid Length) | Guard Triggered | "Invalid Target Length" | Prevents Empty-String Match |
Count Text Cells Matching Exact Length N = COUNTIF(Range, REPT("?", N))
Guards D2 <= 0 because REPT("?", 0) returns "" which would count blank cells instead of 0-character text!
=IF(D2 <= 0, "Invalid Target Length", COUNTIF(B2:B7, REPT("?", D2)))Count Non-Compliant Text Entries Not Equal to Length N
Subtracts valid N-character text matches from COUNTA(B2:B7) to surface malformed SKU strings.
=IF(D2 <= 0, "Invalid Target Length", COUNTA(B2:B7) - COUNTIF(B2:B7, REPT("?", D2)))Try the example
Use the original values or change them to test your understanding.