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



"ryguy7272" wrote:

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

Regards,
Ryan--

--
RyGuy


"PCLIVE" wrote:

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:D"&ROW(D2)),D2)

Then you would use this formula to your 917 numbers counting each
number
only once.
=SUMPRODUCT(--(LEFT(D2:D100,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

--

"Diane1477" wrote in message
...
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.

"ryguy7272" wrote:

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

Regards,
Ryan--

--
RyGuy


"Diane1477" wrote:

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((D2:D123<"")/COUNTIF(D2:D123,D2:D123&""))

Any suggestions on would be greatly appreciated!