# help with countif

#### allan221

##### New Member
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.

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### Yard

##### Well-known Member
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

##### Well-known Member
Actually, your formula works.

Excel Workbook
AB
13999-333-111
14444-444-4444
15999-333-111
16333-333-1111
17999-333-111
18999-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

##### Well-known Member
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

##### Well-known Member
Yep, you're right. It's always text.

#### allan221

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

Replies
12
Views
719
Replies
7
Views
353
Replies
1
Views
278
Replies
5
Views
1K
Replies
1
Views
266

1,190,703
Messages
5,982,395
Members
439,777
Latest member
daleEH

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back