ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   count if unique entries (https://www.excelbanter.com/excel-worksheet-functions/194813-count-if-unique-entries.html)

Tonybagodonuts
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


Gary''s Student
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


T. Valko
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

ryguy7272
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


All times are GMT +1. The time now is 03:23 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com