View Single Post
  #4   Report Post  
Domenic
 
Posts: n/a
Default

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!