Count If And Contains

[Diane1477]

I have a list of phone numbers in column D. I want to count how many of them
begin with a 917 area code excluding duplicates. I then want to subtract that
result from the total number of phone numbers.

I currently use the following formula just to count the number of total
phone numbers excluding dupicates:
=SUMPRODUCT((D2123<>"")/COUNTIF(D2123,D2123&""))

Any suggestions on would be greatly appreciated!

 

[ryguy7272]

Try this:
=SUMPRODUCT(ISNUMBER(SEARCH("*917*",A1:A5))*((B1:B5))/COUNTIF(A1:A5,A1:A5&""))

Regards,
Ryan--

 

[Diane1477]

It's not quite working. Can you advise whatthe formula would be to just count
the 917 numbers?
Also,what is the column B reference for in your formula? I am a bit
confused. My numbers are only in column D.

 

[PCLIVE]

There is probably another way to to it, but here is one way using a helper
column.

If you use column E as your helper column, insert this formula in E2 and
copy down as needed.
=COUNTIF(INDIRECT("D2"&ROW(D2)),D2)

Then you would use this formula to your 917 numbers counting each number
only once.
=SUMPRODUCT(--(LEFT(D2100,3)="917"),--(E2:E100=1))

The above formula assumes that your phone numbers are in a 10 digit format
without parenthesis. Also, I've only used a range up to 100. Adjust as
needed.

HTH,
Paul

--

 

[ryguy7272]

This may be a little better for your specific need:
=SUMPRODUCT(--(ISNUMBER((SEARCH("*917*",A1:A5))*((A1:A5)))))

Regards,
Ryan--

 

[Diane1477]

That calculated the number of 917#s perfectly, notw how fo I get it to not
vount the duplicates?

 

[PCLIVE]

RyGuy,

I haven't tested this. But wouldn't your formula also be counting the
numbers that don't necessarily begin with "917", but have "917" somewhere
else in the phone number?

Ex. 7577917237
917 is in the phone number, but it is not the area code.




--

 

[Fred Smith]

To count numbers that begin with 917 (as opposed to contains 917), just
check for a match in the first 3 characters.

One way to take out the duplicates is to sort the column, and then create a
column (say, B) which compares the number to the preceding row, as in:

=a2=a1

Now you just want to count rows where column B is false. Combining these two
ideas gives:

=SUMPRODUCT(--(LEFT(A1:A5,3)="917"),--(B1:B5=FALSE))

Regards,
Fred

 

[Rick Rothstein \(MVP - VB\)]

That calculated the number of 917#s perfectly, notw how fo I get it

to not vount the duplicates?

I'm not so sure that is true. Try it where the number 917 is located in a
position other than the area code location... I think you will find it is
still counted.

I'm a little confused as to what format your data is in. The formula you
thought worked seems to indicate it is ########## (all digits, no dashes or
parentheses), is that correct? If not, what form or forms can your phone
numbers be in?

Rick