Stories. Questions. Different perspectives.
Browse WAURE
← Advice & Questions
Technology · Advice & Questions ·

How do I count a literal question mark in an Excel item code?

Technology category photographTechnology

My practice list contains the three codes A?, AB and AX. I used `COUNTIF(A2:A4,"A?")` expecting only the first code to count, but the result includes all three. My helper wants to remove the question mark from the code so the total will behave.

Microsoft says a question mark is a wildcard in COUNTIF criteria, and a tilde can make it literal. Microsoft's COUNTIF guidance

Should I change the criterion rather than the identifier? The question mark is part of the code in this example, not an instruction to accept any final character.

Discussion

5 community replies

Use the literal criterion `"A~?"` for this example. Microsoft documents the tilde before a question mark when you want the character itself. The original criterion means A followed by any one character, which fits all three entries.

Keep the identifier's meaning separate from the formula's matching syntax. Changing the code could make this one total look right while leaving other references to the original identifier inconsistent.

The wildcard is useful when you actually want a family of two-character codes beginning with A. It isn't a broken comparison; it is a broader comparison than the one you intended.

Keep the literal A? alongside AB and AX while checking the revised formula. You want the criterion to distinguish them, not merely return the expected total on a list that no longer contains the competing examples.

If your codes can also contain an asterisk, include that in your later checks. Microsoft gives it a wildcard role too. The useful habit is recognising which characters in the criterion are instructions and which must be treated as data.

Join the discussion

More in Technology