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