help with countif

[allan221]

Hi,

I have a set of data(phone numbers) in one column and i want to count the numbers starting with a specified 3 digit. im using this formula: =COUNTIF(A:A,"966*") to count all the phone numbers in column A that is starting with 966 and its always giving me 0 results. Obviously there's something wrong with my formula. Please help me. Thanks in advance.

 

[Yard]

Hi,

Your values are numbers, so the wildcard text character isn't applicable. You either need an array formula to convert your values to text or just try

=SUMPRODUCT(--(LEFT(A1:A5,3)="966"))

(Do you really need to count the whole of column A? Don't use the whole column in the SUMPRODUCT.)

 

[jbeaucaire]

Actually, your formula works.

Excel Workbook
	A	B
13	999-333-111
14	444-444-444	4
15	999-333-111
16	333-333-1111
17	999-333-111
18	999-333-111
Sheet2

If it's not working for you, then I'd suspect your data isn't as clean as you'd need. It's working when I enter the numbers as numbers and as text.

 

[Yard]

Hi, I'm not sure that's the case. Isn't the inclusion of the hyphen characters in your example converting the "number" to text, thereby making the wildcard a valid criteria for the countif? I can't make the COUNTIF work if the values are truly numbers.

 

[jbeaucaire]

Yep, you're right. It's always text.

 

[allan221]

Thanks a lot Yard. The =SUMPRODUCT(--(LEFT(A1:A5,3)="966")) formula works!