Counting dates

Alan C

Active Member
Joined
Feb 19, 2002
Messages
264
In a column of dates, with many duplicates, how can I count the number of individual dates, not the total number in the column?
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top