Count If And Contains
 

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!

Count If And Contains
 

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!

Count If And Contains
 

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!

Count If And Contains
 

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!

Count If And Contains
 

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!

Count If And Contains
 

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

"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!

Count If And Contains
 

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.




--

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

Count If And Contains
 

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



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




--

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

Count If And Contains
 

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!