help with countif

allan221

New Member
Joined
Mar 12, 2009
Messages
2
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
Joined
Nov 5, 2008
Messages
1,929
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.)
 
Upvote 0

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
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.
 
Upvote 0

Yard

Well-known Member
Joined
Nov 5, 2008
Messages
1,929
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.
 
Upvote 0

Forum statistics

Threads
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?

Disable AdBlock

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
Top