Counting individual dates

[Guest]

I have a column of dates, some dates are repeated, and I would like a count
of the number of separate dates.

My sample column:
1/1/05
1/1/05
1/2/05

I want my return to be 2, because I have 2 different dates.

Is there a way to do this?

Thanx!

 

[Domenic]

Try...

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

Hope this helps!

 

[Guest]

THANX Domenic... that did the trick! Now, where can I go to understand that
formula?

 

[Domenic]

Assuming...

A1: 1/1/05
A2: 1/1/05
A3:
A4: 1/2/05

Formula...

=SUMPRODUCT((A1:A4<>"")/COUNTIF(A1:A4,A1:A4&""))

Here's how it breaks down...

(A1:A4<>"") returns the following array of values:

TRUE
TRUE
FALSE
TRUE

COUNTIF(A1:A4,A1:A4&"") returns the following array of numbers:

2
2
1
1

(A1:A4<>"")/COUNTIF(A1:A4,A1:A4&"") returns the following array of
numbers:

0.5
0.5
0
1

Note that the numerical equivalent of TRUE and FALSE is 1 and 0,
respectively. So the first array of values are divided by the second
array of values...

TRUE/2 -----> 0.5
TRUE/2 -----> 0.5
FALSE/1 ----> 0
TRUE/1 -----> 1

Then, lastly, SUMPRODUCT sums these values and returns 2.

Hope this helps!

 

[terabar]

Excellent breakdown explanation....

Never did understood the SUMPRODUCT() either... now, I guess I can use
this too!!

 

[RagDyer]

This will give you a detailed explanation of the function and it's uses:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================