Math
Count and sum GL codes by the sixth character with positional wildcards
Use a sixth-character mask with literal target escaping, and sum matching numeric amounts.
Why "*4*" overcounts—and how "?" locks an exact character slot!
The pattern GL-?? plus one target character plus * matches the fixed GL- prefix, exactly two characters, the selected sixth character and any suffix. A suffix can be empty; this is a minimum-six-character pattern, not validation of an exact seven-character account code.
D2 must represent one character. A numeric digit is converted to text; blank or multi-character inputs are rejected. Escape literal ~, * and ? in D2 so they do not become wildcard instructions. Other question marks and the final asterisk in the fixed pattern remain intentional wildcards.
Matching is case-insensitive and preserves spaces. Keep source codes as text so leading zeros and their positions are retained. The summary does not establish that an account code is valid under an accounting scheme. Numeric amounts may be zero or negative; this example rejects missing or nonnumeric amounts before summing.
| Account Code (Col A) | Spend (Col B) | 6th Character | Matches "GL-??4*"? |
|---|---|---|---|
| GL-1042 | $1,500 | "4" | YES (Included) |
| GL-2048 | $2,300 | "4" | YES (Included) |
| GL-1054 | $900 | "5" ("4" is 7th!) | NO (Excluded cleanly!) |
| GL-3041 | $1,200 | "4" | YES (Total = 3 codes, $5,000) |
| GL-4099 | $800 | "9" ("4" is 4th!) | NO (Excluded cleanly!) |
Count Structured Codes by Exact Nth Character Slot
Uses two "?" placeholders after "GL-" to isolate the 6th character position.
=IF(OR(COUNTBLANK(D2)>0,LEN(D2&"")<>1),"Enter one character",COUNTIF(A2:A6,"GL-??"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2&"","~","~~"),"*","~*"),"?","~?")&"*"))Sum Amounts by Exact Nth Character Slot
Applies the identical positional "?" mask inside SUMIF.
=IF(OR(COUNTBLANK(D2)>0,LEN(D2&"")<>1),"Enter one character",IF(COUNT(B2:B6)<>5,"Invalid amounts",SUMIF(A2:A6,"GL-??"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2&"","~","~~"),"*","~*"),"?","~?")&"*",B2:B6)))Try the example
Use the original values or change them to test your understanding.