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