Counting individual dates
 

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!

Try...

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

Hope this helps!

In article ,
"Robin" wrote:

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!

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

"Domenic" wrote:

Try...

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

Hope this helps!

In article ,
"Robin" wrote:

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!

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!

In article ,
"Robin" wrote:

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

"Domenic" wrote:

Try...

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

Hope this helps!

In article ,
"Robin" wrote:

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!

Excellent breakdown explanation....

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


--
terabar
------------------------------------------------------------------------
terabar's Profile: http://www.excelforum.com/member.php...o&userid=24272
View this thread: http://www.excelforum.com/showthread...hreadid=379192

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!
==============================================



"terabar" wrote in
message ...

Excellent breakdown explanation....

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


--
terabar
------------------------------------------------------------------------
terabar's Profile:
http://www.excelforum.com/member.php...o&userid=24272
View this thread: http://www.excelforum.com/showthread...hreadid=379192