Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Bob is offline
external usenet poster
 
Posts: 972
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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JMB JMB is offline
external usenet poster
 
Posts: 2,062
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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,688
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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JMB JMB is offline
external usenet poster
 
Posts: 2,062
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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,688
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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 208
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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,688
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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 605
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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 605
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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
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







  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default Counting Unique Values


"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.


They are essentially the same formula. You can simplify it by removing the ^
2 and testing for blan k

=SUM((A1:A15<"")/IF(NOT(COUNTIF(A1:A15,A1:A15)),1,COUNTIF(A1:A15,A1 :A15)))

which starts to look more like the SP version.



  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JMB JMB is offline
external usenet poster
 
Posts: 2,062
Default Counting Unique Values

I got the same answer with
=SUMPRODUCT(--(FREQUENCY(A2:A10,A2:A10)0))
as the original formula. If the data was text and I wanted to use
Frequency, I think you would have to use the formula you posted. Frequency
doesn't like text, but it ignores blanks - hence the IF(Len(..), Match(..),
"") combination.

If dealing with a contiguous range, however, using Sumproduct/Countif should
work w/both numbers and text (and you can include or exclude blanks) - so I
wouldn't use Frequency.

I think Frequency would be useful for dealing w/non-contigous cells
(containing numeric data - I think it was Harlan I saw use it for that
purpose).

From testing a little, it seems Frequency ignores blanks. If data is
numeric and you want to evaluate D3:E5 and G7:H9, then

=SUMPRODUCT(--(FREQUENCY((D3:E5,G7:H9),(D3:E5,G7:H9))0))

Not sure about if the cells are non-contiguous and the data is text.
Frequency doesn't like text and Len/Match don't seem to work w/two
dimensional arrays, much less non-contiguous cells - just based on my
observations, which doesn't mean someone can't do it.



"Epinn" wrote:

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







  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 605
Default Counting Unique Values

Bob,

Thank you for asking the question. I have learned a lot. I hope you have got what you wanted. You asked for a formula and I think you have a choice of two formulae now (COUNTIF and FREQUENCY). Please feel free to post any followup questions.

Bob P.,

Thank you for the formula. It looks smarter now. I never like ^2. The last paragraph of your standard reply is precious. I know the purpose of &"" but I don't think there is really an explanation for its formation. I'll take it as part of the syntax.

JMB,

Thank you for taking the time to experiment with FREQUENCY. I appreciate it very much.

I think Frequency would be useful for dealing w/non-contiguous cells
(containing numeric data ...........).


If data is
numeric and you want to evaluate D3:E5 and G7:H9, then


=SUMPRODUCT(--(FREQUENCY((D3:E5,G7:H9),(D3:E5,G7:H9))0))


This information is invaluable. I never thought of "non-contiguous" arrays. The formula on my record looks like this

{=SUM(IF(FREQUENCY(A2:A10,A2:A10)0,1))} and your use of SUMPRODUCT makes it look smarter.


Bob's question is on dates which are numeric. So, I think the following formula which takes care of blanks can serve his purpose.

=SUMPRODUCT(--(FREQUENCY(A1:A15,A1:A15)0))

I tested it and it gave the same results as =SUMPRODUCT((A1:A15<"")/COUNTIF(A1:A15,A1:A15&""))

Bob can pick and choose now.

Thank you all for a wonderful lesson.

Epinn

"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

  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default Counting Unique Values

The ^2 is necessary in the first formula because COUNTIF(A1:A15,A1:A15)
returns a count a of each instance of each item. (A1:A15<"") just returns a
TRUE/FALSE for each instance. Both are divided by the count of the
instances, but as the first is already a count of the instances, it has to
be squared so as to get the correct fractional components.

--
HTH

Bob Phillips

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

"Epinn" wrote in message
...
Bob,

Thank you for asking the question. I have learned a lot. I hope you have
got what you wanted. You asked for a formula and I think you have a choice
of two formulae now (COUNTIF and FREQUENCY). Please feel free to post any
followup questions.

Bob P.,

Thank you for the formula. It looks smarter now. I never like ^2. The
last paragraph of your standard reply is precious. I know the purpose of
&"" but I don't think there is really an explanation for its formation.
I'll take it as part of the syntax.

JMB,

Thank you for taking the time to experiment with FREQUENCY. I appreciate it
very much.

I think Frequency would be useful for dealing w/non-contiguous cells
(containing numeric data ...........).


If data is
numeric and you want to evaluate D3:E5 and G7:H9, then


=SUMPRODUCT(--(FREQUENCY((D3:E5,G7:H9),(D3:E5,G7:H9))0))


This information is invaluable. I never thought of "non-contiguous" arrays.
The formula on my record looks like this

{=SUM(IF(FREQUENCY(A2:A10,A2:A10)0,1))} and your use of SUMPRODUCT makes it
look smarter.


Bob's question is on dates which are numeric. So, I think the following
formula which takes care of blanks can serve his purpose.

=SUMPRODUCT(--(FREQUENCY(A1:A15,A1:A15)0))

I tested it and it gave the same results as
=SUMPRODUCT((A1:A15<"")/COUNTIF(A1:A15,A1:A15&""))

Bob can pick and choose now.

Thank you all for a wonderful lesson.

Epinn



  #15   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 605
Default Counting Unique Values

The formulae discussed in this thread should serve Bob's needs.

Experts, please confirm that the formulae are only good as long as there are no trailing spaces.

e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice.

If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right?

Epinn

"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



  #16   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 605
Default Counting Unique Values

This is how I would remove trailing spaces.

=SUBSTITUTE(A1,CHAR(32),REPT("",255))

Please correct me if I am wrong. Thanks.

Epinn

"Epinn" wrote in message ...
The formulae discussed in this thread should serve Bob's needs.

Experts, please confirm that the formulae are only good as long as there are no trailing spaces.

e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice.

If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right?

Epinn

"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


  #17   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Rob Rob is offline
external usenet poster
 
Posts: 234
Default Counting Unique Values

Epinn,

The following formula entered as an array i.e. Ctrl+Shift+Enter will
correctly report 4 unique dates.

=SUM(1/COUNTIF(A1:A9,A1:A9))

HTH Rob

"Epinn" wrote in message
...
The formulae discussed in this thread should serve Bob's needs.

Experts, please confirm that the formulae are only good as long as there are
no trailing spaces.

e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice.

If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right?

Epinn

"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


  #18   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 605
Default Counting Unique Values

Try test your formula with a range that contains a blank and you'll know why the experts and I won't recommend it. Also, I am a fan of SUMPRODUCT, so I'll use SUMPRODUCT instead of SUM and CSE if there are no blanks.

Thanks for dropping by and sharing.

Epinn

"Rob" wrote in message ...
Epinn,

The following formula entered as an array i.e. Ctrl+Shift+Enter will
correctly report 4 unique dates.

=SUM(1/COUNTIF(A1:A9,A1:A9))

HTH Rob

"Epinn" wrote in message
...
The formulae discussed in this thread should serve Bob's needs.

Experts, please confirm that the formulae are only good as long as there are
no trailing spaces.

e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice.

If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right?

Epinn

"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



  #19   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,886
Default Counting Unique Values

Absolutely no difference to the much simpler
=SUBSTITUTE(A1," ","")

--
Regards

Roger Govier


"Epinn" wrote in message
...
This is how I would remove trailing spaces.

=SUBSTITUTE(A1,CHAR(32),REPT("",255))

Please correct me if I am wrong. Thanks.

Epinn

"Epinn" wrote in message
...
The formulae discussed in this thread should serve Bob's needs.

Experts, please confirm that the formulae are only good as long as there
are no trailing spaces.

e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice.

If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right?

Epinn

"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



  #20   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default Counting Unique Values

Don't understand this comment Epinn:
(emphasis mine)
<<<"so I'll use SUMPRODUCT instead of SUM and CSE *IF THERE ARE NO
BLANKS*."

The Sumproduct() formula Biff posted *will work* with blanks!

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Epinn" wrote in message
...
Try test your formula with a range that contains a blank and you'll know why
the experts and I won't recommend it. Also, I am a fan of SUMPRODUCT, so
I'll use SUMPRODUCT instead of SUM and CSE if there are no blanks.

Thanks for dropping by and sharing.

Epinn

"Rob" wrote in message
...
Epinn,

The following formula entered as an array i.e. Ctrl+Shift+Enter will
correctly report 4 unique dates.

=SUM(1/COUNTIF(A1:A9,A1:A9))

HTH Rob

"Epinn" wrote in message
...
The formulae discussed in this thread should serve Bob's needs.

Experts, please confirm that the formulae are only good as long as there are
no trailing spaces.

e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice.

If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right?

Epinn

"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





  #21   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 605
Default Counting Unique Values

Roger,

Ah, that's why you are the expert. (I laughed at my formula after I have seen yours.) I am glad I posted and you responded. Lately, I have been learning about CHAR and I am attracted to it while I have forgotten the simple things in life. <g I thought I needed REPT to catch all. I read an example of *adding* trailing spaces and REPT is used. Is it true that we need REPT when adding trailing spaces?

Thank you for helping me all the time. By the way, have you read my post about using PivotTable to check for duplicates?

Epinn

"Roger Govier" wrote in message ...
Absolutely no difference to the much simpler
=SUBSTITUTE(A1," ","")

--
Regards

Roger Govier


"Epinn" wrote in message
...
This is how I would remove trailing spaces.

=SUBSTITUTE(A1,CHAR(32),REPT("",255))

Please correct me if I am wrong. Thanks.

Epinn

"Epinn" wrote in message
...
The formulae discussed in this thread should serve Bob's needs.

Experts, please confirm that the formulae are only good as long as there
are no trailing spaces.

e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice.

If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right?

Epinn

"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




  #22   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 605
Default Counting Unique Values

RD,

Thank you for asking. Sometimes I thought people could read my mind. <g

What I was trying to say is this:-

Rob's formula =SUM(1/COUNTIF(A1:A9,A1:A9)) works fine when there are no blanks in the array. However, since I am a fan of SUMPRODUCT, I'll use

=SUMPRODUCT(1/COUNTIF(A1:A9,A1:A9)) instead of

=SUM(1/COUNTIF(A1:A9,A1:A9)) + CSE

even if there are no blanks in the array.

The Sumproduct() formula Biff posted *will work* with blanks! <<


Totally agree. I am very much aware of what each formula in this thread does as I have spent hours experimenting. Biff's formula is what I live by when it comes to counting unique values. It just doesn't take care of blanks but much more. As JMB highlighted, unlike the FREQUENCY formula which takes care of blanks and numbers, Biff's formula will take care of *text*, numbers and blanks. Sounds like a one-fits-all formula. I should memorize it by heart. <g

Now my turn to ask you. What do you mean by "(emphasis mine)?"

By the way, is Ragdyer your last name or first and last name combined i.e. Rag = first name and Dyer = last name? I won't ask you about your gender. <g

Epinn

"Ragdyer" wrote in message ...
Don't understand this comment Epinn:
(emphasis mine)
<<<"so I'll use SUMPRODUCT instead of SUM and CSE *IF THERE ARE NO
BLANKS*."

The Sumproduct() formula Biff posted *will work* with blanks!

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Epinn" wrote in message
...
Try test your formula with a range that contains a blank and you'll know why
the experts and I won't recommend it. Also, I am a fan of SUMPRODUCT, so
I'll use SUMPRODUCT instead of SUM and CSE if there are no blanks.

Thanks for dropping by and sharing.

Epinn

"Rob" wrote in message
...
Epinn,

The following formula entered as an array i.e. Ctrl+Shift+Enter will
correctly report 4 unique dates.

=SUM(1/COUNTIF(A1:A9,A1:A9))

HTH Rob

"Epinn" wrote in message
...
The formulae discussed in this thread should serve Bob's needs.

Experts, please confirm that the formulae are only good as long as there are
no trailing spaces.

e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice.

If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right?

Epinn

"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




  #23   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default Counting Unique Values



"Epinn" wrote in message
...
RD,


By the way, is Ragdyer your last name or first and last name combined i.e.
Rag = first name and Dyer = last name? I won't ask you about your gender.
<g

http://tinyurl.com/y2kzhm


  #24   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default Counting Unique Values

When quoting a statement, the perception is that the quote is the *exact*
depiction of the original.
However, to accentuate and bring attention to a portion of the quote, the
quote may be *modified*, and is therefore no longer an *exact* depiction of
the original (you didn't capitalize and wrap those words in asterisks).
It is proper decorum to bring attention to the modifications made to the
quote by the person using the quote.
"emphasis mine" is describing that *I* changed the quote by emphasizing a
portion of it.

Ragdyer is my "handle", going back to the old days of CB radio.
I carried it forward to the "new" internet.
It just describes my profession of being in the textile dyeing trade.
By coincidence, RD does not stand for RagDyer, but my actual name of Rick
Dormack.

I just continued to use my handle exclusively, when I was told by some here
that I *shouldn't*!<bg
You can surmise by my name that I'm male.

Now, why all the secrecy about your identity?

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------



"Epinn" wrote in message
...
RD,

Thank you for asking. Sometimes I thought people could read my mind. <g

What I was trying to say is this:-

Rob's formula =SUM(1/COUNTIF(A1:A9,A1:A9)) works fine when there are no
blanks in the array. However, since I am a fan of SUMPRODUCT, I'll use

=SUMPRODUCT(1/COUNTIF(A1:A9,A1:A9)) instead of

=SUM(1/COUNTIF(A1:A9,A1:A9)) + CSE

even if there are no blanks in the array.

The Sumproduct() formula Biff posted *will work* with blanks! <<


Totally agree. I am very much aware of what each formula in this thread
does as I have spent hours experimenting. Biff's formula is what I live by
when it comes to counting unique values. It just doesn't take care of
blanks but much more. As JMB highlighted, unlike the FREQUENCY formula
which takes care of blanks and numbers, Biff's formula will take care of
*text*, numbers and blanks. Sounds like a one-fits-all formula. I should
memorize it by heart. <g

Now my turn to ask you. What do you mean by "(emphasis mine)?"

By the way, is Ragdyer your last name or first and last name combined i.e.
Rag = first name and Dyer = last name? I won't ask you about your gender.
<g

Epinn

"Ragdyer" wrote in message
...
Don't understand this comment Epinn:
(emphasis mine)
<<<"so I'll use SUMPRODUCT instead of SUM and CSE *IF THERE ARE NO
BLANKS*."

The Sumproduct() formula Biff posted *will work* with blanks!

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Epinn" wrote in message
...
Try test your formula with a range that contains a blank and you'll know why
the experts and I won't recommend it. Also, I am a fan of SUMPRODUCT, so
I'll use SUMPRODUCT instead of SUM and CSE if there are no blanks.

Thanks for dropping by and sharing.

Epinn

"Rob" wrote in message
...
Epinn,

The following formula entered as an array i.e. Ctrl+Shift+Enter will
correctly report 4 unique dates.

=SUM(1/COUNTIF(A1:A9,A1:A9))

HTH Rob

"Epinn" wrote in message
...
The formulae discussed in this thread should serve Bob's needs.

Experts, please confirm that the formulae are only good as long as there are
no trailing spaces.

e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice.

If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right?

Epinn

"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




  #25   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default Counting Unique Values

You've got a good memory Mr. Phillips!<bg
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Bob Phillips" wrote in message
...


"Epinn" wrote in message
...
RD,


By the way, is Ragdyer your last name or first and last name combined i.e.
Rag = first name and Dyer = last name? I won't ask you about your gender.
<g

http://tinyurl.com/y2kzhm





  #26   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 605
Default Counting Unique Values

Ragdyer is my "handle"......<<

You sound hitech by using the word "handle." When I see the word "handle," I think TINYURL ...... <g

So, you have a story to tell about your user ID as well. Dyer is a very legitimate surname.

If I don't hear a "no," I'll call you Rick from now on. Actually, RD has less keystrokes. Well, let my mood decide.

Epinn

"Ragdyer" wrote in message ...
When quoting a statement, the perception is that the quote is the *exact*
depiction of the original.
However, to accentuate and bring attention to a portion of the quote, the
quote may be *modified*, and is therefore no longer an *exact* depiction of
the original (you didn't capitalize and wrap those words in asterisks).
It is proper decorum to bring attention to the modifications made to the
quote by the person using the quote.
"emphasis mine" is describing that *I* changed the quote by emphasizing a
portion of it.

Ragdyer is my "handle", going back to the old days of CB radio.
I carried it forward to the "new" internet.
It just describes my profession of being in the textile dyeing trade.
By coincidence, RD does not stand for RagDyer, but my actual name of Rick
Dormack.

I just continued to use my handle exclusively, when I was told by some here
that I *shouldn't*!<bg
You can surmise by my name that I'm male.

Now, why all the secrecy about your identity?

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------



"Epinn" wrote in message
...
RD,

Thank you for asking. Sometimes I thought people could read my mind. <g

What I was trying to say is this:-

Rob's formula =SUM(1/COUNTIF(A1:A9,A1:A9)) works fine when there are no
blanks in the array. However, since I am a fan of SUMPRODUCT, I'll use

=SUMPRODUCT(1/COUNTIF(A1:A9,A1:A9)) instead of

=SUM(1/COUNTIF(A1:A9,A1:A9)) + CSE

even if there are no blanks in the array.

The Sumproduct() formula Biff posted *will work* with blanks! <<


Totally agree. I am very much aware of what each formula in this thread
does as I have spent hours experimenting. Biff's formula is what I live by
when it comes to counting unique values. It just doesn't take care of
blanks but much more. As JMB highlighted, unlike the FREQUENCY formula
which takes care of blanks and numbers, Biff's formula will take care of
*text*, numbers and blanks. Sounds like a one-fits-all formula. I should
memorize it by heart. <g

Now my turn to ask you. What do you mean by "(emphasis mine)?"

By the way, is Ragdyer your last name or first and last name combined i.e.
Rag = first name and Dyer = last name? I won't ask you about your gender.
<g

Epinn

"Ragdyer" wrote in message
...
Don't understand this comment Epinn:
(emphasis mine)
<<<"so I'll use SUMPRODUCT instead of SUM and CSE *IF THERE ARE NO
BLANKS*."

The Sumproduct() formula Biff posted *will work* with blanks!

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Epinn" wrote in message
...
Try test your formula with a range that contains a blank and you'll know why
the experts and I won't recommend it. Also, I am a fan of SUMPRODUCT, so
I'll use SUMPRODUCT instead of SUM and CSE if there are no blanks.

Thanks for dropping by and sharing.

Epinn

"Rob" wrote in message
...
Epinn,

The following formula entered as an array i.e. Ctrl+Shift+Enter will
correctly report 4 unique dates.

=SUM(1/COUNTIF(A1:A9,A1:A9))

HTH Rob

"Epinn" wrote in message
...
The formulae discussed in this thread should serve Bob's needs.

Experts, please confirm that the formulae are only good as long as there are
no trailing spaces.

e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice.

If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right?

Epinn

"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





  #27   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default Counting Unique Values

You very nonchalantly bypassed *my* question though!<g
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Epinn" wrote in message
...
Ragdyer is my "handle"......<<


You sound hitech by using the word "handle." When I see the word "handle,"
I think TINYURL ...... <g

So, you have a story to tell about your user ID as well. Dyer is a very
legitimate surname.

If I don't hear a "no," I'll call you Rick from now on. Actually, RD has
less keystrokes. Well, let my mood decide.

Epinn

"Ragdyer" wrote in message
...
When quoting a statement, the perception is that the quote is the *exact*
depiction of the original.
However, to accentuate and bring attention to a portion of the quote, the
quote may be *modified*, and is therefore no longer an *exact* depiction of
the original (you didn't capitalize and wrap those words in asterisks).
It is proper decorum to bring attention to the modifications made to the
quote by the person using the quote.
"emphasis mine" is describing that *I* changed the quote by emphasizing a
portion of it.

Ragdyer is my "handle", going back to the old days of CB radio.
I carried it forward to the "new" internet.
It just describes my profession of being in the textile dyeing trade.
By coincidence, RD does not stand for RagDyer, but my actual name of Rick
Dormack.

I just continued to use my handle exclusively, when I was told by some here
that I *shouldn't*!<bg
You can surmise by my name that I'm male.

Now, why all the secrecy about your identity?

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------



"Epinn" wrote in message
...
RD,

Thank you for asking. Sometimes I thought people could read my mind. <g

What I was trying to say is this:-

Rob's formula =SUM(1/COUNTIF(A1:A9,A1:A9)) works fine when there are no
blanks in the array. However, since I am a fan of SUMPRODUCT, I'll use

=SUMPRODUCT(1/COUNTIF(A1:A9,A1:A9)) instead of

=SUM(1/COUNTIF(A1:A9,A1:A9)) + CSE

even if there are no blanks in the array.

The Sumproduct() formula Biff posted *will work* with blanks! <<


Totally agree. I am very much aware of what each formula in this thread
does as I have spent hours experimenting. Biff's formula is what I live by
when it comes to counting unique values. It just doesn't take care of
blanks but much more. As JMB highlighted, unlike the FREQUENCY formula
which takes care of blanks and numbers, Biff's formula will take care of
*text*, numbers and blanks. Sounds like a one-fits-all formula. I should
memorize it by heart. <g

Now my turn to ask you. What do you mean by "(emphasis mine)?"

By the way, is Ragdyer your last name or first and last name combined i.e.
Rag = first name and Dyer = last name? I won't ask you about your gender.
<g

Epinn

"Ragdyer" wrote in message
...
Don't understand this comment Epinn:
(emphasis mine)
<<<"so I'll use SUMPRODUCT instead of SUM and CSE *IF THERE ARE NO
BLANKS*."

The Sumproduct() formula Biff posted *will work* with blanks!

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Epinn" wrote in message
...
Try test your formula with a range that contains a blank and you'll know why
the experts and I won't recommend it. Also, I am a fan of SUMPRODUCT, so
I'll use SUMPRODUCT instead of SUM and CSE if there are no blanks.

Thanks for dropping by and sharing.

Epinn

"Rob" wrote in message
...
Epinn,

The following formula entered as an array i.e. Ctrl+Shift+Enter will
correctly report 4 unique dates.

=SUM(1/COUNTIF(A1:A9,A1:A9))

HTH Rob

"Epinn" wrote in message
...
The formulae discussed in this thread should serve Bob's needs.

Experts, please confirm that the formulae are only good as long as there are
no trailing spaces.

e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice.

If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right?

Epinn

"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





  #28   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 605
Default Counting Unique Values

I think I'll use TRIM( ) if you have no objection.

Epinn

"Roger Govier" wrote in message ...
Absolutely no difference to the much simpler
=SUBSTITUTE(A1," ","")

--
Regards

Roger Govier


"Epinn" wrote in message
...
This is how I would remove trailing spaces.

=SUBSTITUTE(A1,CHAR(32),REPT("",255))

Please correct me if I am wrong. Thanks.

Epinn

"Epinn" wrote in message
...
The formulae discussed in this thread should serve Bob's needs.

Experts, please confirm that the formulae are only good as long as there
are no trailing spaces.

e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice.

If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right?

Epinn

"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




  #29   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default Counting Unique Values

That is because I too was fascinated by your handle, so I looked it up, and
I remember that it was in response to Stephen :-).


Bob

"Ragdyer" wrote in message
...
You've got a good memory Mr. Phillips!<bg
--
Regards,

RD

--------------------------------------------------------------------------

-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------

-
"Bob Phillips" wrote in message
...


"Epinn" wrote in message
...
RD,


By the way, is Ragdyer your last name or first and last name combined

i.e.
Rag = first name and Dyer = last name? I won't ask you about your

gender.
<g

http://tinyurl.com/y2kzhm





  #30   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default Counting Unique Values


"Ragdyer" wrote in message
...

I just continued to use my handle exclusively, when I was told by some

here
that I *shouldn't*!<bg



Whoever suggested that? I remember when 'onedaywhen' was outed as Jamie
Collins, I always thought it was a shame that he dropped the handle, it gave
a certain panache to his posts. I hope you never drop RagDyer, it adds
diversity to the group.




  #31   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 593
Default Counting Unique Values

Bob Phillips wrote:
I remember when 'onedaywhen' was outed as Jamie
Collins, I always thought it was a shame that he dropped the handle, it gave
a certain panache to his posts.


Google doesn't seem to want me to drop the handle either, Bob. I've
long since removed it as a nickname from my profile, however it still
gets applied unless I've subscribed to a group (whatever that means).
So about half my replies are attributed to onedaywhen (try a google
search of recent posts).

FWIW I reverted to using my given name due to negative feedback: one
person thought it was a threat, another suggested it was a pseudonym to
hide behind. Neither was the case. A so-called 'real' name seems to be
preferred.

Jamie.

--

  #32   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default Counting Unique Values

Jamie,

Good to hear from you again. I think I remember you telling me that once
before, odd isn't it?

That negative feedback you refer to was presumably the same sort of rubbish
that Ragdyer was getting. He's more stubborn than you though <G.

Anyway, I hope that you are well and all is good in ADO/SQL-land.

"Jamie Collins" wrote in message
oups.com...
Bob Phillips wrote:
I remember when 'onedaywhen' was outed as Jamie
Collins, I always thought it was a shame that he dropped the handle, it

gave
a certain panache to his posts.


Google doesn't seem to want me to drop the handle either, Bob. I've
long since removed it as a nickname from my profile, however it still
gets applied unless I've subscribed to a group (whatever that means).
So about half my replies are attributed to onedaywhen (try a google
search of recent posts).

FWIW I reverted to using my given name due to negative feedback: one
person thought it was a threat, another suggested it was a pseudonym to
hide behind. Neither was the case. A so-called 'real' name seems to be
preferred.

Jamie.

--



  #33   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 605
Default Counting Unique Values

It is amazing that Jamie could hear Bob mention his/her name and showed up "instantly."

RD, I like handles and you know why. Is it true that whoever knows your real name is in your inner circle? <bg

Ricky D. wrote: "Now, why all the secrecy about your identity?......
............................You very nonchalantly bypassed *my* question though!<g"

Response: Come visit me at

MARS-o-Soft
1010111000111010100010101010^9

}......}...... Epinn, call home ......{......{

Got to go.

Happy Halloween to all!


"Ragdyer" wrote in message ...
You very nonchalantly bypassed *my* question though!<g
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Epinn" wrote in message
...
Ragdyer is my "handle"......<<


You sound hitech by using the word "handle." When I see the word "handle,"
I think TINYURL ...... <g

So, you have a story to tell about your user ID as well. Dyer is a very
legitimate surname.

If I don't hear a "no," I'll call you Rick from now on. Actually, RD has
less keystrokes. Well, let my mood decide.

Epinn

"Ragdyer" wrote in message
...
When quoting a statement, the perception is that the quote is the *exact*
depiction of the original.
However, to accentuate and bring attention to a portion of the quote, the
quote may be *modified*, and is therefore no longer an *exact* depiction of
the original (you didn't capitalize and wrap those words in asterisks).
It is proper decorum to bring attention to the modifications made to the
quote by the person using the quote.
"emphasis mine" is describing that *I* changed the quote by emphasizing a
portion of it.

Ragdyer is my "handle", going back to the old days of CB radio.
I carried it forward to the "new" internet.
It just describes my profession of being in the textile dyeing trade.
By coincidence, RD does not stand for RagDyer, but my actual name of Rick
Dormack.

I just continued to use my handle exclusively, when I was told by some here
that I *shouldn't*!<bg
You can surmise by my name that I'm male.

Now, why all the secrecy about your identity?

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------



"Epinn" wrote in message
...
RD,

Thank you for asking. Sometimes I thought people could read my mind. <g

What I was trying to say is this:-

Rob's formula =SUM(1/COUNTIF(A1:A9,A1:A9)) works fine when there are no
blanks in the array. However, since I am a fan of SUMPRODUCT, I'll use

=SUMPRODUCT(1/COUNTIF(A1:A9,A1:A9)) instead of

=SUM(1/COUNTIF(A1:A9,A1:A9)) + CSE

even if there are no blanks in the array.

The Sumproduct() formula Biff posted *will work* with blanks! <<


Totally agree. I am very much aware of what each formula in this thread
does as I have spent hours experimenting. Biff's formula is what I live by
when it comes to counting unique values. It just doesn't take care of
blanks but much more. As JMB highlighted, unlike the FREQUENCY formula
which takes care of blanks and numbers, Biff's formula will take care of
*text*, numbers and blanks. Sounds like a one-fits-all formula. I should
memorize it by heart. <g

Now my turn to ask you. What do you mean by "(emphasis mine)?"

By the way, is Ragdyer your last name or first and last name combined i.e.
Rag = first name and Dyer = last name? I won't ask you about your gender.
<g

Epinn

"Ragdyer" wrote in message
...
Don't understand this comment Epinn:
(emphasis mine)
<<<"so I'll use SUMPRODUCT instead of SUM and CSE *IF THERE ARE NO
BLANKS*."

The Sumproduct() formula Biff posted *will work* with blanks!

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Epinn" wrote in message
...
Try test your formula with a range that contains a blank and you'll know why
the experts and I won't recommend it. Also, I am a fan of SUMPRODUCT, so
I'll use SUMPRODUCT instead of SUM and CSE if there are no blanks.

Thanks for dropping by and sharing.

Epinn

"Rob" wrote in message
...
Epinn,

The following formula entered as an array i.e. Ctrl+Shift+Enter will
correctly report 4 unique dates.

=SUM(1/COUNTIF(A1:A9,A1:A9))

HTH Rob

"Epinn" wrote in message
...
The formulae discussed in this thread should serve Bob's needs.

Experts, please confirm that the formulae are only good as long as there are
no trailing spaces.

e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice.

If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right?

Epinn

"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






  #34   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default Counting Unique Values


What did Shakespeare say:

What's in a name?
A rose is a rose ... or something like that.

Anyway, don't fret, we'll let diversity live on!<bg

And I must configure my O.E. to enable me to pop up like Jamie did, at the
mere mention of his name or handle.

Regards,

RD

"Bob Phillips" wrote in message
...

"Ragdyer" wrote in message
...

I just continued to use my handle exclusively, when I was told by some

here
that I *shouldn't*!<bg



Whoever suggested that? I remember when 'onedaywhen' was outed as Jamie
Collins, I always thought it was a shame that he dropped the handle, it
gave
a certain panache to his posts. I hope you never drop RagDyer, it adds
diversity to the group.



  #35   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default Counting Unique Values

If that "Come Visit Me At" is some kind of a test,
I've failed miserably!<g
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Epinn" wrote in message
...
It is amazing that Jamie could hear Bob mention his/her name and showed up
"instantly."

RD, I like handles and you know why. Is it true that whoever knows your
real name is in your inner circle? <bg

Ricky D. wrote: "Now, why all the secrecy about your identity?......
............................You very nonchalantly bypassed *my* question
though!<g"

Response: Come visit me at

MARS-o-Soft
1010111000111010100010101010^9

}......}...... Epinn, call home ......{......{

Got to go.

Happy Halloween to all!


"Ragdyer" wrote in message
...
You very nonchalantly bypassed *my* question though!<g
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Epinn" wrote in message
...
Ragdyer is my "handle"......<<


You sound hitech by using the word "handle." When I see the word "handle,"
I think TINYURL ...... <g

So, you have a story to tell about your user ID as well. Dyer is a very
legitimate surname.

If I don't hear a "no," I'll call you Rick from now on. Actually, RD has
less keystrokes. Well, let my mood decide.

Epinn

"Ragdyer" wrote in message
...
When quoting a statement, the perception is that the quote is the *exact*
depiction of the original.
However, to accentuate and bring attention to a portion of the quote, the
quote may be *modified*, and is therefore no longer an *exact* depiction of
the original (you didn't capitalize and wrap those words in asterisks).
It is proper decorum to bring attention to the modifications made to the
quote by the person using the quote.
"emphasis mine" is describing that *I* changed the quote by emphasizing a
portion of it.

Ragdyer is my "handle", going back to the old days of CB radio.
I carried it forward to the "new" internet.
It just describes my profession of being in the textile dyeing trade.
By coincidence, RD does not stand for RagDyer, but my actual name of Rick
Dormack.

I just continued to use my handle exclusively, when I was told by some here
that I *shouldn't*!<bg
You can surmise by my name that I'm male.

Now, why all the secrecy about your identity?

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------



"Epinn" wrote in message
...
RD,

Thank you for asking. Sometimes I thought people could read my mind. <g

What I was trying to say is this:-

Rob's formula =SUM(1/COUNTIF(A1:A9,A1:A9)) works fine when there are no
blanks in the array. However, since I am a fan of SUMPRODUCT, I'll use

=SUMPRODUCT(1/COUNTIF(A1:A9,A1:A9)) instead of

=SUM(1/COUNTIF(A1:A9,A1:A9)) + CSE

even if there are no blanks in the array.

The Sumproduct() formula Biff posted *will work* with blanks! <<


Totally agree. I am very much aware of what each formula in this thread
does as I have spent hours experimenting. Biff's formula is what I live by
when it comes to counting unique values. It just doesn't take care of
blanks but much more. As JMB highlighted, unlike the FREQUENCY formula
which takes care of blanks and numbers, Biff's formula will take care of
*text*, numbers and blanks. Sounds like a one-fits-all formula. I should
memorize it by heart. <g

Now my turn to ask you. What do you mean by "(emphasis mine)?"

By the way, is Ragdyer your last name or first and last name combined i.e.
Rag = first name and Dyer = last name? I won't ask you about your gender.
<g

Epinn

"Ragdyer" wrote in message
...
Don't understand this comment Epinn:
(emphasis mine)
<<<"so I'll use SUMPRODUCT instead of SUM and CSE *IF THERE ARE NO
BLANKS*."

The Sumproduct() formula Biff posted *will work* with blanks!

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Epinn" wrote in message
...
Try test your formula with a range that contains a blank and you'll know why
the experts and I won't recommend it. Also, I am a fan of SUMPRODUCT, so
I'll use SUMPRODUCT instead of SUM and CSE if there are no blanks.

Thanks for dropping by and sharing.

Epinn

"Rob" wrote in message
...
Epinn,

The following formula entered as an array i.e. Ctrl+Shift+Enter will
correctly report 4 unique dates.

=SUM(1/COUNTIF(A1:A9,A1:A9))

HTH Rob

"Epinn" wrote in message
...
The formulae discussed in this thread should serve Bob's needs.

Experts, please confirm that the formulae are only good as long as there are
no trailing spaces.

e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice.

If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right?

Epinn

"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








  #36   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default Counting Unique Values

I think that whilst Jamie doesn't inhabit anymore, he secretly misses it, so
keeps a watch on :-)

Bob

"RagDyer" wrote in message
...

What did Shakespeare say:

What's in a name?
A rose is a rose ... or something like that.

Anyway, don't fret, we'll let diversity live on!<bg

And I must configure my O.E. to enable me to pop up like Jamie did, at the
mere mention of his name or handle.

Regards,

RD

"Bob Phillips" wrote in message
...

"Ragdyer" wrote in message
...

I just continued to use my handle exclusively, when I was told by some

here
that I *shouldn't*!<bg



Whoever suggested that? I remember when 'onedaywhen' was outed as Jamie
Collins, I always thought it was a shame that he dropped the handle, it
gave
a certain panache to his posts. I hope you never drop RagDyer, it adds
diversity to the group.





  #37   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 605
Default Counting Unique Values

Off Topic - You can ignore.

Bob and RD,

Is it true that we have to type Jamie's full name, Jamie Collins, otherwise he won't "hear" us?

"What's in a name? That which we call a rose

By any other word would smell as sweet."

--From Romeo and Juliet (II, ii, 1-2) <<

I better apologize to those who don't like distraction, before anyone yells at me. From now on, I will include "Off Topic" in the beginning of the post to alert readers. I have seen a MS forum not as focused as this one. ;) I guess it's okay if I don't make this a habit.

Epinn

"Bob Phillips" wrote in message ...
I think that whilst Jamie doesn't inhabit anymore, he secretly misses it, so
keeps a watch on :-)

Bob

"RagDyer" wrote in message
...

What did Shakespeare say:

What's in a name?
A rose is a rose ... or something like that.

Anyway, don't fret, we'll let diversity live on!<bg

And I must configure my O.E. to enable me to pop up like Jamie did, at the
mere mention of his name or handle.

Regards,

RD

"Bob Phillips" wrote in message
...

"Ragdyer" wrote in message
...

I just continued to use my handle exclusively, when I was told by some

here
that I *shouldn't*!<bg



Whoever suggested that? I remember when 'onedaywhen' was outed as Jamie
Collins, I always thought it was a shame that he dropped the handle, it
gave
a certain panache to his posts. I hope you never drop RagDyer, it adds
diversity to the group.






  #38   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 593
Default Counting Unique Values


Bob Phillips wrote:
I hope that you are well and all is good in ADO/SQL-land.


Yes thanks, Bob. Belated congratulations on your MVP award (hey, why
doesn't your face show up in photos <g?)

Jamie.

--

  #39   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 593
Default Counting Unique Values


Epinn wrote:
I think that whilst Jamie doesn't inhabit anymore, he secretly misses it, so
keeps a watch on :-)


Is it true that we have to type Jamie's full name, Jamie Collins, otherwise he won't "hear" us?


This is what I 'hear':

http://groups.google.com/groups/sear...osoft.public.*

Via my RSS reader, I still 'listen' to Daily Dose of Excel
(http://www.dicks-blog.com/), otherwise I don't really have time for
Excel: I can spend only a finite time on coffee break. Anyhow, I was in
danger becoming part of the Excel establishment myself (Groucho and
club membership etc).

Jamie.

--

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Counting unique values with criteria Kevin McCartney Excel Worksheet Functions 10 December 31st 09 05:02 PM
list unique values in a column beechum1 Excel Worksheet Functions 1 March 2nd 06 06:08 AM
Populate combo box with unique values only sjayar Excel Discussion (Misc queries) 1 November 7th 05 08:29 AM
Counting Values Alan Excel Worksheet Functions 6 June 9th 05 07:33 AM
Counting unique values + frequency Mike Ferguson Excel Worksheet Functions 3 November 24th 04 08:31 AM


All times are GMT +1. The time now is 12:06 PM.

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

About Us

"It's about Microsoft Excel"