| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count if unique entries
I need to count the number of unique entries (names) in a column of data, one
count for each different name. List changes throghout the day and so I can't keep putting names in a long 'countif' formula. Am looking for something that will identify a unique entry and count it. This type of list: Doctor Mahoubi, Ray Corcoran, David Aguilera, Ruben Green, Kerry S. Aguilera, Ruben Sarraf, Payam Murad, Craig A. Murad, Craig A. Murad, Craig A. Murad, Craig A. Murad, Craig A. Murad, Craig A. Thull, David A. Murad, Craig A. Murad, Craig A. Crezee, Kelvin S. Gorman, Ike B. Gorman, Ike B. Gorman, Ike B. Sarraf, Payam Turner, Merle C. Turner, Merle C. Turner, Merle C. Jensen, Scott M. Turner, Merle C. Turner, Merle C. Jensen, Scott M. Discont, Alan J. Discont, Alan J. Discont, Alan J. Discont, Alan J. Pawlowski, Jeff V. Pawlowski, Jeff V. Pawlowski, Jeff V. Jacoby, Richard Jacoby, Richard Jacoby, Richard Jacoby, Richard Jacoby, Richard Beecroft, C. Jon Beecroft, C. Jon Sapin, Neil J. Aguilera, Ruben Mehta, Kishor D. Aguilera, Ruben Green, Kerry S. Green, Kerry S. Green, Kerry S. Hoang, Phat D. Hoang, Phat D. Freed, Lewis H. Freed, Lewis H. Mehta, Kishor D. Mehta, Kishor D. Jacoby, Richard Thull, David A. Thull, David A. Thull, David A. Jensen, Scott M. Turner, Merle C. Turner, Merle C. Turner, Merle C. Discont, Alan J. Krand, Greg |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count if unique entries
Use a Pivot Table. See:
http://contextures.com/xlPivot07.html#Unique -- Gary''s Student - gsnu200794 "Tonybagodonuts" wrote: I need to count the number of unique entries (names) in a column of data, one count for each different name. List changes throghout the day and so I can't keep putting names in a long 'countif' formula. Am looking for something that will identify a unique entry and count it. This type of list: Doctor Mahoubi, Ray Corcoran, David Aguilera, Ruben Green, Kerry S. Aguilera, Ruben Sarraf, Payam Murad, Craig A. Murad, Craig A. Murad, Craig A. Murad, Craig A. Murad, Craig A. Murad, Craig A. Thull, David A. Murad, Craig A. Murad, Craig A. Crezee, Kelvin S. Gorman, Ike B. Gorman, Ike B. Gorman, Ike B. Sarraf, Payam Turner, Merle C. Turner, Merle C. Turner, Merle C. Jensen, Scott M. Turner, Merle C. Turner, Merle C. Jensen, Scott M. Discont, Alan J. Discont, Alan J. Discont, Alan J. Discont, Alan J. Pawlowski, Jeff V. Pawlowski, Jeff V. Pawlowski, Jeff V. Jacoby, Richard Jacoby, Richard Jacoby, Richard Jacoby, Richard Jacoby, Richard Beecroft, C. Jon Beecroft, C. Jon Sapin, Neil J. Aguilera, Ruben Mehta, Kishor D. Aguilera, Ruben Green, Kerry S. Green, Kerry S. Green, Kerry S. Hoang, Phat D. Hoang, Phat D. Freed, Lewis H. Freed, Lewis H. Mehta, Kishor D. Mehta, Kishor D. Jacoby, Richard Thull, David A. Thull, David A. Thull, David A. Jensen, Scott M. Turner, Merle C. Turner, Merle C. Turner, Merle C. Discont, Alan J. Krand, Greg |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count if unique entries
Try this:
=SUMPRODUCT((A2:A100<"")/COUNTIF(A2:A100,A2:A100&"")) Note that this will be "slow" on large amounts of data. You could notice the "slowness" at around ~2000 cells or greater. -- Biff Microsoft Excel MVP "Tonybagodonuts" wrote in message ... I need to count the number of unique entries (names) in a column of data, one count for each different name. List changes throghout the day and so I can't keep putting names in a long 'countif' formula. Am looking for something that will identify a unique entry and count it. This type of list: Doctor Mahoubi, Ray Corcoran, David Aguilera, Ruben Green, Kerry S. Aguilera, Ruben Sarraf, Payam Murad, Craig A. Murad, Craig A. Murad, Craig A. Murad, Craig A. Murad, Craig A. Murad, Craig A. Thull, David A. Murad, Craig A. Murad, Craig A. Crezee, Kelvin S. Gorman, Ike B. Gorman, Ike B. Gorman, Ike B. Sarraf, Payam Turner, Merle C. Turner, Merle C. Turner, Merle C. Jensen, Scott M. Turner, Merle C. Turner, Merle C. Jensen, Scott M. Discont, Alan J. Discont, Alan J. Discont, Alan J. Discont, Alan J. Pawlowski, Jeff V. Pawlowski, Jeff V. Pawlowski, Jeff V. Jacoby, Richard Jacoby, Richard Jacoby, Richard Jacoby, Richard Jacoby, Richard Beecroft, C. Jon Beecroft, C. Jon Sapin, Neil J. Aguilera, Ruben Mehta, Kishor D. Aguilera, Ruben Green, Kerry S. Green, Kerry S. Green, Kerry S. Hoang, Phat D. Hoang, Phat D. Freed, Lewis H. Freed, Lewis H. Mehta, Kishor D. Mehta, Kishor D. Jacoby, Richard Thull, David A. Thull, David A. Thull, David A. Jensen, Scott M. Turner, Merle C. Turner, Merle C. Turner, Merle C. Discont, Alan J. Krand, Greg |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count if unique entries
I can come up with at least 9 ways to do this; any more is moot:
=SUMPRODUCT((A1:A78<"")/(COUNTIF(A1:A78,A1:A78)+(A1:A78=""))) =SUM(IF(FREQUENCY(IF(LEN(A1:A971)0,MATCH(A1:A971, A1:A971,0),""),IF(LEN(A1:A971)0,MATCH(A1:A971,A1: A971,0),""))0,1)) (this is a CSE function; you must hit Ctrl + Shift + Enter at the same time) =SUM(IF(A1:A400<"",1/COUNTIF(A1:A400,A1:A400))) (this is a CSE function; you must hit Ctrl + Shift + Enter at the same time) =SUMPRODUCT((A1:A78<"")/(COUNTIF(A1:A78,A1:A78&""))) (this is a CSE function; you must hit Ctrl + Shift + Enter at the same time) =SUM(--(FREQUENCY(IF(A1:A2676<"",MATCH(A1:A2676,A1:A2676 ,0)),ROW(INDIRECT("1:"&ROWS(A1:A2676))))0)) (this is a CSE function; you must hit Ctrl + Shift + Enter at the same time) Regards, Ryan--- -- RyGuy "T. Valko" wrote: Try this: =SUMPRODUCT((A2:A100<"")/COUNTIF(A2:A100,A2:A100&"")) Note that this will be "slow" on large amounts of data. You could notice the "slowness" at around ~2000 cells or greater. -- Biff Microsoft Excel MVP "Tonybagodonuts" wrote in message ... I need to count the number of unique entries (names) in a column of data, one count for each different name. List changes throghout the day and so I can't keep putting names in a long 'countif' formula. Am looking for something that will identify a unique entry and count it. This type of list: Doctor Mahoubi, Ray Corcoran, David Aguilera, Ruben Green, Kerry S. Aguilera, Ruben Sarraf, Payam Murad, Craig A. Murad, Craig A. Murad, Craig A. Murad, Craig A. Murad, Craig A. Murad, Craig A. Thull, David A. Murad, Craig A. Murad, Craig A. Crezee, Kelvin S. Gorman, Ike B. Gorman, Ike B. Gorman, Ike B. Sarraf, Payam Turner, Merle C. Turner, Merle C. Turner, Merle C. Jensen, Scott M. Turner, Merle C. Turner, Merle C. Jensen, Scott M. Discont, Alan J. Discont, Alan J. Discont, Alan J. Discont, Alan J. Pawlowski, Jeff V. Pawlowski, Jeff V. Pawlowski, Jeff V. Jacoby, Richard Jacoby, Richard Jacoby, Richard Jacoby, Richard Jacoby, Richard Beecroft, C. Jon Beecroft, C. Jon Sapin, Neil J. Aguilera, Ruben Mehta, Kishor D. Aguilera, Ruben Green, Kerry S. Green, Kerry S. Green, Kerry S. Hoang, Phat D. Hoang, Phat D. Freed, Lewis H. Freed, Lewis H. Mehta, Kishor D. Mehta, Kishor D. Jacoby, Richard Thull, David A. Thull, David A. Thull, David A. Jensen, Scott M. Turner, Merle C. Turner, Merle C. Turner, Merle C. Discont, Alan J. Krand, Greg |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count unique entries... | Excel Worksheet Functions | |||
| Count Unique Entries | Excel Worksheet Functions | |||
| Count unique entries | Excel Discussion (Misc queries) | |||
| Count unique entries | Excel Worksheet Functions | |||
| Count of unique entries | Excel Discussion (Misc queries) | |||
Linear Mode
