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