number of unique dates in a range

Date is an oject as any other. The following applies to dates too:

Array-enter:

=SUM(IF(LEN(A1:A10),1/COUNTIF(A1:A10,A1:A10)))

Note. In order to array-enter a formula you need to hit control+shift+enter at the same time, not just enter.

===========

Thanks Aladin, works a treat, but what's the LEN part of the formula doing?

It works, no prob, but why? What does the LEN part do?
Chris

Re: Thanks Aladin, works a treat, but what's the LEN part of the formula doing?

Start in a clean worksheet.

In A1 enter: za
In A2 enter: xa
In A3 enter: za

In C1 array- enter: {=SUM(1/COUNTIF(A1:A3,A1:A3))}

Go the Formula Bar, select the COUNTIF(A1:A3,A1:A3) part of the formula, and hit F9. What you see is the counts associated with each entry: "za" gets a count of 2, "xa" 1. Now select the 1/COUNTIF(A1:A3,A1:A3) and hit F9.

What you see is:

=SUM({0.5;1;0.5})

where 1 is divided by occurrence counts. The division creates, as it were, weighted value for each occurrence or token. When these values are summed you get the count you're interested in.

Now leave A4 blank.

In A5 enter: =IF(B1,1,"") [ This creates a formula generated blank. ]

In A6 enter: da

In C2 array-enter: {=SUM(IF(ISBLANK(A1:A5),1/COUNTIF(A1:A5,A1:A5)))}

What do you get?

In C3 array-enter: {=SUM(IF(LEN(A1:A5),1/COUNTIF(A1:A5,A1:A5)))}

What do you get?

LEN copes better with blanks however they are generated than ISBLANK. As it should be clear by now, the IF(LEN.. part filters blanks of any sort (uninteresting as type) before it gives the entries to the real part that does the counting.

Hope this helps.

Aladin