A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Counting Unique Values



 
 
Thread Tools Display Modes
  #1  
Old October 23rd, 2006, 02:55 AM posted to microsoft.public.excel.worksheet.functions
Bob
external usenet poster
 
Posts: 1,351
Default Counting Unique Values

I have a column with the following dates:

11/23/05
11/23/05
11/23/05
11/25/05
11/25/05
11/28/05
12/1/05
12/1/05
12/1/05

I want a formula that will tell me how many unique dates there are in the
column. Can anyone help?

thanks,
Bob
  #2  
Old October 23rd, 2006, 03:07 AM posted to microsoft.public.excel.worksheet.functions
JMB
external usenet poster
 
Posts: 1,266
Default Counting Unique Values

If memory serves, I believe I've seen this formula suggested by others. It
should ignore blank cells. Change range as needed.

=SUMPRODUCT(--(A1:A6""),1/COUNTIF(A1:A6,A1:A6&""))

If there are no empty cells in your data you could shorten it to
=SUMPRODUCT(1/COUNTIF(A1:A5,A1:A5))

"bob" wrote:

I have a column with the following dates:

11/23/05
11/23/05
11/23/05
11/25/05
11/25/05
11/28/05
12/1/05
12/1/05
12/1/05

I want a formula that will tell me how many unique dates there are in the
column. Can anyone help?

thanks,
Bob

  #3  
Old October 23rd, 2006, 03:07 AM posted to microsoft.public.excel.worksheet.functions
Biff
external usenet poster
 
Posts: 1,559
Default Counting Unique Values

Try this:

=SUMPRODUCT(--(A1:A9"")/COUNTIF(A1:A9,A1:A9&""))

Biff

"bob" wrote in message
...
I have a column with the following dates:

11/23/05
11/23/05
11/23/05
11/25/05
11/25/05
11/28/05
12/1/05
12/1/05
12/1/05

I want a formula that will tell me how many unique dates there are in the
column. Can anyone help?

thanks,
Bob



  #4  
Old October 23rd, 2006, 03:23 AM posted to microsoft.public.excel.worksheet.functions
JMB
external usenet poster
 
Posts: 1,266
Default Counting Unique Values

Don't tell me, I know - the 1 is superfluous g.

"Biff" wrote:

Try this:

=SUMPRODUCT(--(A1:A9"")/COUNTIF(A1:A9,A1:A9&""))

Biff

"bob" wrote in message
...
I have a column with the following dates:

11/23/05
11/23/05
11/23/05
11/25/05
11/25/05
11/28/05
12/1/05
12/1/05
12/1/05

I want a formula that will tell me how many unique dates there are in the
column. Can anyone help?

thanks,
Bob




  #5  
Old October 23rd, 2006, 03:57 AM posted to microsoft.public.excel.worksheet.functions
Biff
external usenet poster
 
Posts: 1,559
Default Counting Unique Values

I only do that when I'm bored and there's nothing to play with!

Biff

"JMB" wrote in message
...
Don't tell me, I know - the 1 is superfluous g.

"Biff" wrote:

Try this:

=SUMPRODUCT(--(A1:A9"")/COUNTIF(A1:A9,A1:A9&""))

Biff

"bob" wrote in message
...
I have a column with the following dates:

11/23/05
11/23/05
11/23/05
11/25/05
11/25/05
11/28/05
12/1/05
12/1/05
12/1/05

I want a formula that will tell me how many unique dates there are in
the
column. Can anyone help?

thanks,
Bob






  #6  
Old October 23rd, 2006, 06:01 AM posted to microsoft.public.excel.worksheet.functions
PapaDos
external usenet poster
 
Posts: 139
Default Counting Unique Values

LOL
We can also question the '--', can't we ?
;-]
--
Regards,
Luc.

"Festina Lente"


"JMB" wrote:

Don't tell me, I know - the 1 is superfluous g.

"Biff" wrote:

Try this:

=SUMPRODUCT(--(A1:A9"")/COUNTIF(A1:A9,A1:A9&""))

Biff

"bob" wrote in message
...
I have a column with the following dates:

11/23/05
11/23/05
11/23/05
11/25/05
11/25/05
11/28/05
12/1/05
12/1/05
12/1/05

I want a formula that will tell me how many unique dates there are in the
column. Can anyone help?

thanks,
Bob




  #7  
Old October 23rd, 2006, 06:46 AM posted to microsoft.public.excel.worksheet.functions
Biff
external usenet poster
 
Posts: 1,559
Default Counting Unique Values

We can also question the '--', can't we ?

Yes! Touche!

Biff

"PapaDos" wrote in message
...
LOL
We can also question the '--', can't we ?
;-]
--
Regards,
Luc.

"Festina Lente"


"JMB" wrote:

Don't tell me, I know - the 1 is superfluous g.

"Biff" wrote:

Try this:

=SUMPRODUCT(--(A1:A9"")/COUNTIF(A1:A9,A1:A9&""))

Biff

"bob" wrote in message
...
I have a column with the following dates:

11/23/05
11/23/05
11/23/05
11/25/05
11/25/05
11/28/05
12/1/05
12/1/05
12/1/05

I want a formula that will tell me how many unique dates there are in
the
column. Can anyone help?

thanks,
Bob





  #8  
Old October 23rd, 2006, 07:58 AM posted to microsoft.public.excel.worksheet.functions
Epinn
external usenet poster
 
Posts: 754
Default Counting Unique Values

Biff,

That's brilliant! Some time ago, I found the following array formula suggested by an expert.

{=SUM(COUNTIF(A1:A15,A1:A15)/IF(NOT(COUNTIF(A1:A15,A1:A15)),1,COUNTIF(A1:A15,A1 :A15))^2)}

Like your formula, it takes care of blanks in the array as well. Can you confirm that both your formula and the above formula do exactly the same thing i.e. count unique text and numeric values in a range which may contain blanks. If yes, I'll replace the above with your formula.

Regarding the formula

=SUMPRODUCT((A1:A15"")/COUNTIF(A1:A15,A1:A15&""))

can you explain the &"" part please? I know if I remove &"" I will get the #DIV/0! error. But I can't tell from "evaluate formula" what &"" is doing?

One more question:-

I know from my record and JMB's comment that this formula

=SUMPRODUCT(1/COUNTIF(A1:A15,A1:A15))

will give an error when there is a blank in the array. When I change it to

=SUMPRODUCT(1/COUNTIF(A1:A15,A1:A15&""))

I get a result which counts the blank(s) as well. I guess if I want blanks counted, I can use this formula, right?

Another alternative to count unique records is to use Advanced filter, unique records, and COUNTA. This doesn't require any analysis and understanding.

Biff, I look forward to your guidance. Thanks!

Epinn

"Biff" wrote in message ...
Try this:

=SUMPRODUCT(--(A1:A9"")/COUNTIF(A1:A9,A1:A9&""))

Biff

"bob" wrote in message
...
I have a column with the following dates:

11/23/05
11/23/05
11/23/05
11/25/05
11/25/05
11/28/05
12/1/05
12/1/05
12/1/05

I want a formula that will tell me how many unique dates there are in the
column. Can anyone help?

thanks,
Bob




  #9  
Old October 23rd, 2006, 08:13 AM posted to microsoft.public.excel.worksheet.functions
Epinn
external usenet poster
 
Posts: 754
Default Counting Unique Values

I only do that when I'm bored and there's nothing to play with!

Do you want to play with the following?

{=SUM(IF(FREQUENCY(IF(LEN(A2:A10)0,MATCH(A2:A10,A 2:A10,0),""), IF(LEN(A2:A10)0,MATCH(A2:A10,A2:A10,0),""))0,1)) }

If I remember correctly, the above is from Help which usually tells us the "inefficient" way of doing things. Glad to have someone like you, JMB and Luc around. By the way, Luc, you were brilliant the other day switching roles between the LOOKUP table and the data set (LOOKUP values).

Bob, I hope you don't mind me showing up. Also, please ignore the formulae I posted because I don't want to confuse you.

Epinn


"Biff" wrote in message ...
I only do that when I'm bored and there's nothing to play with!

Biff

"JMB" wrote in message
...
Don't tell me, I know - the 1 is superfluous g.

"Biff" wrote:

Try this:

=SUMPRODUCT(--(A1:A9"")/COUNTIF(A1:A9,A1:A9&""))

Biff

"bob" wrote in message
...
I have a column with the following dates:

11/23/05
11/23/05
11/23/05
11/25/05
11/25/05
11/28/05
12/1/05
12/1/05
12/1/05

I want a formula that will tell me how many unique dates there are in
the
column. Can anyone help?

thanks,
Bob







  #10  
Old October 23rd, 2006, 09:42 AM posted to microsoft.public.excel.worksheet.functions
Bob Phillips
external usenet poster
 
Posts: 5,994
Default Counting Unique Values

This is my standard reply to this question

Let's start by defining the range A1:A20 to talk specifics.

Bob,John,Bob,Bob,John,John,Bob,Bill,Bill,Max


or data in just A1:A10


The basic formula to count unique items is
=SUMPRODUCT(1/COUNTIF($A$1:$A$10,$A$1:$A$10))
The COUNTIF($A$1:$A$10,$A$1:$A$10) part of the formula builds an array of
the number of occurrences of each item, in this case{4;3;4;4;3;3;4;2;2;1}.
As can be seen, each occurrence of the repeated value is counted, so there
are four occurrences of Bob in the array. There will always be the same
number of occurrences of value as the count of that value, unless two or
more items are repeated the same number of times, in which case it will be
some multiple of that count.
Thus the item that is repeated 4 times has 4 instances of that count,
dividing 1 by the count of 4, gives 0.25 4 times. The full array of values
is
{0.25;0.333333333333333;0.25;0.25;0.33333333333333 3;0.333333333333333;0.25;0
..5;0.5;1}.
The item that repeats 4 times sums to 1. The item that repeats 3 times also
sums to 1. It should be clear from this that every value works in the same
way and sums to 1. In other words, 1 is returned for every unique item. The
sum of these values becomes the count of unique items.
As our test range is A1:A20, and some of the items in A1:A20 are blank,
extending this formula to A1:A20 would return a #DIV/0! Error.
The reason for the error is blank cells in the full range A1:A20. Each blank
cell returns a 0 value from the COUNTIF formula, which gives the #DIV/0!
Error when divided into 1.
The solution to this is to force it to count the empty cells as well, and
not return a zero. Adding &"" to the end of the COUNTIF formula forces a
count of the blanks.
This addition on its own removes the #DIV/0! error, but will cause the
blanks to be counted as a unique item. A further addition to the formula
resolves this by testing for those blanks. Instead of dividing the array of
counts into 1 each time, adding the test creates an array of TRUE/FALSE
values to be divided by the equivalent element in the counts array. Each
blank will resolve to FALSE in the dividend array, and the count of the
blanks in the divisor array. The result of this will be 0, so the blanks do
not get counted.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Epinn" wrote in message
...
Biff,

That's brilliant! Some time ago, I found the following array formula
suggested by an expert.

{=SUM(COUNTIF(A1:A15,A1:A15)/IF(NOT(COUNTIF(A1:A15,A1:A15)),1,COUNTIF(A1:A15
,A1:A15))^2)}

Like your formula, it takes care of blanks in the array as well. Can you
confirm that both your formula and the above formula do exactly the same
thing i.e. count unique text and numeric values in a range which may contain
blanks. If yes, I'll replace the above with your formula.

Regarding the formula

=SUMPRODUCT((A1:A15"")/COUNTIF(A1:A15,A1:A15&""))

can you explain the &"" part please? I know if I remove &"" I will get the
#DIV/0! error. But I can't tell from "evaluate formula" what &"" is doing?

One more question:-

I know from my record and JMB's comment that this formula

=SUMPRODUCT(1/COUNTIF(A1:A15,A1:A15))

will give an error when there is a blank in the array. When I change it to

=SUMPRODUCT(1/COUNTIF(A1:A15,A1:A15&""))

I get a result which counts the blank(s) as well. I guess if I want blanks
counted, I can use this formula, right?

Another alternative to count unique records is to use Advanced filter,
unique records, and COUNTA. This doesn't require any analysis and
understanding.

Biff, I look forward to your guidance. Thanks!

Epinn

"Biff" wrote in message
...
Try this:

=SUMPRODUCT(--(A1:A9"")/COUNTIF(A1:A9,A1:A9&""))

Biff

"bob" wrote in message
...
I have a column with the following dates:

11/23/05
11/23/05
11/23/05
11/25/05
11/25/05
11/28/05
12/1/05
12/1/05
12/1/05

I want a formula that will tell me how many unique dates there are in the
column. Can anyone help?

thanks,
Bob





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:53 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.