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

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

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