Counting dates

[Alan C]

In a column of dates, with many duplicates, how can I count the number of individual dates, not the total number in the column?

[Juan Pablo González]

Try with this Array formula (Assuming dates are in B2:B18)

{=SUM(IF(LEN(B2:B18),1/COUNTIF(B2:B18,B2:B18)))}

It must be entered pressing Control Shift Enter at the same time.

[Aladin Akyurek]

Alan,

I believe you want to assess whether you have duplicate dates in a range of interest, not a count by individual dates.

You can do the assessment by means of a formula and/or identify duplicate dates by using conditional formatting.

Diagnostic formula that you can use is:

=COUNT(A2:A100)-IF(LEN(A2:A100),SUMPRODUCT(1/COUNTIF(A2:A100,A2:A100)))

The number that this formula produces indicates how many date duplicates you have in the range A2:A100, where the first date entry is in A2.

In order to identify duplicates:

Select A2:A100 [ the date range of interest ];
Activate Format|Conditional Formatting;
Choose Formula Is for Condition 1;
Enter in the formula box:

=COUNTIF($A$2:$A$100,A2)>1 [ do not freeze A2, that is, no $-sign wrt A2 ]

Activate Format;
Select a color on the Patterns tab.
Click OK, OK.

Aladin

[Alan C]

Thanks everyone;this is a great help.

[Mark W.]

On 2002-02-20 05:29, Alan C wrote:
In a column of dates, with many duplicates, how can I count the number of individual dates, not the total number in the column?

Here's one more approach...

{=SUM(IF(FREQUENCY(A:A,A:A),1))}

Note: This is an array formula which must be
entered using the Control+Shift+Enter key
combination. The outermost braces, { }, are
not entered by you -- they're supplied by
Excel in recognition of a properly entered
array formula.

[ This Message was edited by: Mark W. on 2002-02-20 06:42 ]