Countif & Contains?

Hi,

I have a series of phone numbers & I'm trying to determine the number of times a particular area was called

864384XXXX
864316XXXX
864288XXXX
864288XXXX
864232XXXX
864384XXXX
864384XXXX

I thought I could use a formula like this: =COUNTIF($E$47:$E$322,"*864*")

(Notice the ' * ')

Where in this column, if the number contained an '864' number, it would count it.

What's wront with this forumula or what is the correct formula?

Thanks

Hi, I don't think countif can use wildcards. You could do something like

=SUMPRODUCT(--(LEFT(A1:A7,3)="864"))

hth

Hi Teylyn,

Thanks for the formula - that worked! Could've sworn though that there was some symbol that worked to act as wildcard in a Countif forumula

Now, I have a similar question, which is the basically the same thing - Find '864' #, but now, total up the minutes.

I tried using this: =SUMIF($E$47:$E$322,"864",$F$47:$F$322), but again, it can't find the '864'

Please advise & thanks again...

try this on your pianola:

=SUMPRODUCT(--(LEFT($E$47:$E$322,3)="864"),$F$47:$F$322)

hth

Must the entire number be in one column or can they be split into area code, prefix and number columns?

Hi Again Teylyn,

Your 2nd formula worked great to!! As for protonLeah's question - No, I cannot seperate number into its own column. I'm trying to track mobile calls & copying & pasting from online phone bill. For the most part, I'm tracking my phone calls & a few other things.

Thanks

Originally Posted by teylyn

Hi, I don't think countif can use wildcards.

Not so, this is one of the great advantages of SUMIF/COUNTIF etc.... so the original formula "should" work:

Originally Posted by mycon73

=COUNTIF($E$47:$E$322,"*864*")

With the aforementioned data set the above would return 7.

If you wish to find only numbers commencing with 864 (implied by Teylyn's SUMPRODUCT which you say works) then change the code to:

Please Login or Register  to view this content.

ie remove the first * - this stipulates that the string must start with 864 - the second * stipulates that what appears thereafter is of no consequence and can be ignored.


The SUMIF utilising the same wildcard approach should answer your 2nd question.

I stand corrected! Shows that I need to study wildcards some more. Thanks for the enlightenment, DO!!