Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Criteria, Count If, Sum Product to get count across range | Excel Worksheet Functions | |||
Count Employee Work Time - Don't Double-count Overlapping Apts. | Excel Worksheet Functions | |||
Excel 2000, count, sort a list & count totals? | Excel Worksheet Functions | |||
Count Intervals of 1 Numeric value in a Row and Return Count down Column | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions |