Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count
I want to count how many different customers are in a column. I have a lot
of duplicate customers, so I need a formula that will tell me how many different ones there are. In my example below the total count is 4 in a total of 5 lines. I.E. adva palm palm john teak |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count
Hi,
Try this =SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&"")) Mike "Carla" wrote: I want to count how many different customers are in a column. I have a lot of duplicate customers, so I need a formula that will tell me how many different ones there are. In my example below the total count is 4 in a total of 5 lines. I.E. adva palm palm john teak |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count
I tried that formula with a column showing numbers 1-10 with your formula,
and it said I only have 9 different items when it should be 10. Now, when I did it with words, it worked correctly. Thank you, Carla "Mike H" wrote: Hi, Try this =SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&"")) Mike "Carla" wrote: I want to count how many different customers are in a column. I have a lot of duplicate customers, so I need a formula that will tell me how many different ones there are. In my example below the total count is 4 in a total of 5 lines. I.E. adva palm palm john teak |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count
You sure you don't have a repeat of one of those numbers?
Returns 10 for me with 1 through 10 in A1:A10 Gord Dibben MS Excel MVP On Wed, 4 Nov 2009 12:38:01 -0800, Carla wrote: I tried that formula with a column showing numbers 1-10 with your formula, and it said I only have 9 different items when it should be 10. Now, when I did it with words, it worked correctly. Thank you, Carla "Mike H" wrote: Hi, Try this =SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&"")) Mike "Carla" wrote: I want to count how many different customers are in a column. I have a lot of duplicate customers, so I need a formula that will tell me how many different ones there are. In my example below the total count is 4 in a total of 5 lines. I.E. adva palm palm john teak |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count
There are many ways to count uniques among duplicates:
=SUMPRODUCT((A2:A105<"")/(COUNTIF(A2:A105,A2:A105&""))) =SUMPRODUCT((A2:A998<"")/(COUNTIF(A2:A998,A2:A998)+(A2:A998=""))) =SUM(IF(FREQUENCY(IF(LEN(A2:A999)0,MATCH(A2:A999, A2:A999,0),""),IF(LEN(A2:A999)0,MATCH(A2:A999,A2: A999,0),""))0,1)) Note: This is a CSE Function =SUMPRODUCT((A2:A105<"")/COUNTIF(A2:A105,A2:A105&"")*(A2:A105<"")) =SUMPRODUCT(--(A2:A999<""),1/COUNTIF(A2:A999,A2:A999&"")) =SUM(--(FREQUENCY(IF(A2:A2705<"",MATCH(A2:A2705,A2:A2705 ,0)),ROW(INDIRECT("1:"&ROWS(A2:A2705))))0)) Note: This is a CSE Function =COUNT(1/FREQUENCY(A1:A400,A1:A400)) =SUM(1/COUNTIF(C13:C17,C13:C17)) Note: This is a CSE Function & it fails if there are any blanks in between =SUM(IF(A2:A400<"",1/COUNTIF(A2:A400,A2:A400))) =SUM(IF(FREQUENCY(IF(A2:A1001<"",MATCH("~"&A2:A10 01,A2:A1001&"",0)),ROW(A2:A1001)-ROW(A2)+1),1)) =IF(COUNT(A1:A20)<ROW(A1),"",INDEX(A1:A20,MATCH(SM ALL(A1:A20,ROW(A1)),A1:A20,0))) HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Carla" wrote: I tried that formula with a column showing numbers 1-10 with your formula, and it said I only have 9 different items when it should be 10. Now, when I did it with words, it worked correctly. Thank you, Carla "Mike H" wrote: Hi, Try this =SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&"")) Mike "Carla" wrote: I want to count how many different customers are in a column. I have a lot of duplicate customers, so I need a formula that will tell me how many different ones there are. In my example below the total count is 4 in a total of 5 lines. I.E. adva palm palm john teak |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count
One way...
If there might be empty cells within the range: =SUMPRODUCT((A2:A6<"")/COUNTIF(A2:A6,A2:A6&"")) If there won't be any empty cells within the range: =SUMPRODUCT(1/COUNTIF(A2:A6,A2:A6)) -- Biff Microsoft Excel MVP "Carla" wrote in message ... I want to count how many different customers are in a column. I have a lot of duplicate customers, so I need a formula that will tell me how many different ones there are. In my example below the total count is 4 in a total of 5 lines. I.E. adva palm palm john teak |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Employee Work Time - Don't Count Duplicates | 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 |