How to count duplicate items in a range in Excel 2000?

[Kevin Stecyk]

Hi TippyToe,

Here is an older CompuServe message which address your question.

Topic: Counting different value Msg #335156



To: Fiona Condon [received] 2 replies



From: Bill Manville 5:45 p.m. on 11-Aug-1999







Hi Fiona



<<

Is there a function that will count the number of different values in a
range? For example, if the range contained the values apples, oranges,
bananas, apples, oranges, bananas, apples, apples, apples, it would return 3
because there are 3 different fruit.



If List is the range,

=SUM(1/COUNTIF(List,List))

entered as an array formula with Ctrl+Shift+Enter



with acknowledgement to David Hager (I think) for a most ingenious solution.



Bill Manville

Oxford, England



Bill Manville was correct, David Hagar is the original creator of the
solution.



Best regards,

Kevin

 

[TippyToe]

Im not sure how to get this result.

I have a column of numeric values. These represent Patient Account numbers.
There may be more than one instance of the same number. I want to know the
total number of Patient account numbers, but, dont want to count the same
number more than once.

Any suggestions? Ive tried the countif function, but got unddesired results,
on some accounts.

 

[Peo Sjoblom]

Probably because you have blanks, try

=SUM(IF(LEN(A1:A100)>0,1/COUNTIF(A1:A100,A1:A100)))

entered with ctrl + shift & enter



--

Regards,

Peo Sjoblom

Hmmm. I used it, and Im getting the #Div/0! error?

I name A1:A500 as rangename, list. This range contains numeric values.
In cell A510, I entered as an array:
=sum(1/countif(list,list))

Did I miss something?

Hi TippyToe,

Here is an older CompuServe message which address your question.

Topic: Counting different value Msg #335156



To: Fiona Condon [received] 2 replies



From: Bill Manville 5:45 p.m. on 11-Aug-1999







Hi Fiona



<<

Is there a function that will count the number of different values in a
range? For example, if the range contained the values apples, oranges,
bananas, apples, oranges, bananas, apples, apples, apples, it would

return

3
because there are 3 different fruit.




If List is the range,

=SUM(1/COUNTIF(List,List))

entered as an array formula with Ctrl+Shift+Enter



with acknowledgement to David Hager (I think) for a most ingenious solution.



Bill Manville

Oxford, England



Bill Manville was correct, David Hagar is the original creator of the
solution.



Best regards,

Kevin

 

[TippyToe]

Hmmm. I used it, and Im getting the #Div/0! error?

I name A1:A500 as rangename, list. This range contains numeric values.
In cell A510, I entered as an array:
=sum(1/countif(list,list))

Did I miss something?

Hi TippyToe,

Here is an older CompuServe message which address your question.

Topic: Counting different value Msg #335156



To: Fiona Condon [received] 2 replies



From: Bill Manville 5:45 p.m. on 11-Aug-1999







Hi Fiona



<<

Is there a function that will count the number of different values in a
range? For example, if the range contained the values apples, oranges,
bananas, apples, oranges, bananas, apples, apples, apples, it would return 3
because there are 3 different fruit.



If List is the range,

=SUM(1/COUNTIF(List,List))

entered as an array formula with Ctrl+Shift+Enter



with acknowledgement to David Hager (I think) for a most ingenious solution.



Bill Manville

Oxford, England



Bill Manville was correct, David Hagar is the original creator of the
solution.



Best regards,

Kevin

Im not sure how to get this result.

I have a column of numeric values. These represent Patient Account numbers.
There may be more than one instance of the same number. I want to know the
total number of Patient account numbers, but, dont want to count the same
number more than once.

Any suggestions? Ive tried the countif function, but got unddesired results,
on some accounts.

 

[TippyToe]

I found the reason. There are some cells within the List range, that are
blank. If I exclude those cells, it works.
Theres not many of them, so I guess I can just sort the range each time,
adjust the range to exclude those rows, and all will be ok.
Thanks for the reply

Hmmm. I used it, and Im getting the #Div/0! error?

I name A1:A500 as rangename, list. This range contains numeric values.
In cell A510, I entered as an array:
=sum(1/countif(list,list))

Did I miss something?

Hi TippyToe,

Here is an older CompuServe message which address your question.

Topic: Counting different value Msg #335156



To: Fiona Condon [received] 2 replies



From: Bill Manville 5:45 p.m. on 11-Aug-1999







Hi Fiona



<<

Is there a function that will count the number of different values in a
range? For example, if the range contained the values apples, oranges,
bananas, apples, oranges, bananas, apples, apples, apples, it would

return

3
because there are 3 different fruit.




If List is the range,

=SUM(1/COUNTIF(List,List))

entered as an array formula with Ctrl+Shift+Enter



with acknowledgement to David Hager (I think) for a most ingenious solution.



Bill Manville

Oxford, England



Bill Manville was correct, David Hagar is the original creator of the
solution.



Best regards,

Kevin

 

[TippyToe]

Thanks Peo. Even better

I found the reason. There are some cells within the List range, that are
blank. If I exclude those cells, it works.
Theres not many of them, so I guess I can just sort the range each time,
adjust the range to exclude those rows, and all will be ok.
Thanks for the reply

Hmmm. I used it, and Im getting the #Div/0! error?

I name A1:A500 as rangename, list. This range contains numeric values.
In cell A510, I entered as an array:
=sum(1/countif(list,list))

Did I miss something?

Hi TippyToe,

Here is an older CompuServe message which address your question.

Topic: Counting different value Msg #335156



To: Fiona Condon [received] 2 replies



From: Bill Manville 5:45 p.m. on 11-Aug-1999







Hi Fiona



<<

Is there a function that will count the number of different values in a
range? For example, if the range contained the values apples, oranges,
bananas, apples, oranges, bananas, apples, apples, apples, it would

return

3
because there are 3 different fruit.





If List is the range,

=SUM(1/COUNTIF(List,List))

entered as an array formula with Ctrl+Shift+Enter



with acknowledgement to David Hager (I think) for a most ingenious solution.



Bill Manville

Oxford, England



Bill Manville was correct, David Hagar is the original creator of the
solution.



Best regards,

Kevin






Im not sure how to get this result.

I have a column of numeric values. These represent Patient Account
numbers.
There may be more than one instance of the same number. I want to

know
the

total number of Patient account numbers, but, dont want to count the same
number more than once.

Any suggestions? Ive tried the countif function, but got unddesired
results,
on some accounts.

 

[Harlan Grove]

Probably because you have blanks, try

=SUM(IF(LEN(A1:A100)>0,1/COUNTIF(A1:A100,A1:A100)))

entered with ctrl + shift & enter

Or maybe

=SUMPRODUCT((A1:A100<>"")/(COUNTIF(A1:A100,A1:A100)+(A1:A100="")))

(X<>"") is more efficient and requires fewer function calls than (LEN(X)>0).