Thanks:  0
Likes:  0

# Thread: Counting dates

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

2. 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.

3. 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

4. Thanks everyone;this is a great help.

5. 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 ]

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•