MrExcel Publishing
Your One Stop for Excel Tips & Solutions

number of unique dates in a range


Posted by Chris Brown on February 04, 2002 8:33 AM

Is there a way to count the number of unique dates in a range? I sure as heck can't figure it.

thanks

Chris


Posted by Aladin Akyurek on February 04, 2002 8:46 AM

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.

===========

Posted by Chris Brown on February 05, 2002 1:29 AM

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

Posted by Aladin Akyurek on February 05, 2002 8:15 AM

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