COUNT using partial info?

[viveleroi0]

I have a column with a thousand phone numbers, but I want to count ho
many contain a certain area code.

This works if the column is just the area code:

=COUNT(CALLER_NUM, "202")

but I need something like:

=COUNT(CALLER_NUM, "202*")

something that tells excel to ignor any character after the *

 

[Frank Kabel]

Hi
try
=COUNTIF(CAller_Num,"202*")

 

[viveleroi0]

I did - that didn't work

 

[Don]

You might try a helper column and use the Data/Text to
Column feature......then apply the Count formula...

 

[Frank Kabel]

Hi
what exactly dows not work?. Also how are your values stored?. As
numbers or as Text.
as alternative try:
=SUMPRODUCT(--(LEFT(A1:A1000,3)=202))

 

[Don]

-----Original Message-----
You might try a helper column and use the Data/Text to
Column feature......then apply the Count formula...

oooooops.....depending on how your numbers are formatted
that might not work....but maybe this..

=LEFT(A1,3)

if the numbers are formatted (XXX) XXX-XXXX

I tried this and it seems to ignore the (

HTH

 

[BenjieLop]

I have a phone list that is in the format

xxx-xxx-xxxx

and Frank's formula works. I double checked it by counting how man
phone numbers have the area code that I entered.

Frank's second formula (=sumproduct ... ) returned a value of 0 though
FYI only.

 

[BenjieLop]

Hi Frank,

I retested this "sumproduct" formula of yours and it worked if the are
code was enclosed in quotes.

As it is, with this formula

=SUMPRODUCT(--(LEFT(A1:A1000,3)=202))

a value of 0 is being returned. However, if the area code will be i
quotes


=SUMPRODUCT(--(LEFT(A1:A1000,3)="202"))

the correct count will be returned.

I just thought I'd let you know ...

 

[viveleroi0]

I got it to work... I had tried =COUNT not =COUNTIF, my mistake. Thanks

 

[Don]

Hi Benji,

I found the same thing after playing with this for a bit.
Also found that no matter what the format or formatting
the helper column with =Left(A1,3) copied down and then
applying the CountIf function to this helper column
worked. Should be able to combine these two but I haven't
found a working combination of the two.

 

[BenjieLop]

Hi Don,

<< I found the same thing after playing with this for a bit.
Also found that no matter what the format or formatting
the helper column with =Left(A1,3) copied down and then
applying the CountIf function to this helper column
worked. >>

Indeed this approach of yours works as well. As I have wrote on
different thread somewhere in this board, there are so many ways t
solve a problem in Excel, with each solution being a valid one.

At least, we know that there are so many Excel functions at ou
disposal and these make things "easier" a little bit.


<< Should be able to combine these two but I haven't
found a working combination of the two. >>

I have tried this as well but, so far, no luck. I might try agai
tomorrow after getting a good night sleep.


-----Original Message-----

Hi Frank,

I retested this "sumproduct" formula of yours and it

worked if the area
code was enclosed in quotes.

As it is, with this formula

=SUMPRODUCT(--(LEFT(A1:A1000,3)=202))

a value of 0 is being returned. However, if the area code will be in
quotes


=SUMPRODUCT(--(LEFT(A1:A1000,3)="202"))

the correct count will be returned.

I just thought I'd let you know ...


[/B[/QUOTE]