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!