Use of wildcards in COUNTIF match expression

I have a list of phone numbers in the familiar format: (aaa) nnn-nnnn where aaa is the area code and n is a digit (0-9). I want to use the COUNTIF function to find the number of phone numbers having the same area code. This requires use of wildcards such as “*” and “?”.

Assume: “range” is a column range (A1:A1000) wherein the phone numbers are stored.

I have tried using a number of match expressions. Match expressions using “" seem to produce reasonable results. For example, = COUNTIF(range, "”) correctly returns the total number of phone numbers in the range. = COUNTIF(range, “765”) returns the total number of phone numbers containing 765.

Consider = COUNTIF(range, “?765*”). This should return the number of phone numbers having area code 765. However, it typically returns a single digit value such as 1 or 2. Why?

Having a list of area codes, one might want to use the CONCAT function in the match expression. For example, = COUNTIF(range, CONCAT("?",A1,"*"). Again, the use of the wildcard “?” does not return the expected result.

Please evaluate and advise. Thank you!

Tools>Options>Calc>Calculate “Enable wildcards in formulas”
All this applies to strings but not numbers, so you should store identifiers (such as phone “numbers”) as text.

1 Like