Count Days?

[wx4usa]

Is there a way to count the specific instances/occurances of dates?

I may run as many as 3 delivery trucks on a specific day. On some days
no trucks run. I want to count how many days in a month I deliver.
Without counting the multiples.

I have a list of dates in column A .
Column a represents data about each truck run.
The other columns show other delivery related data.

01/02/06
01/02/06
01/03/06
01/05/06
02/05/06
02/15/06

etc

Is there a way to count the number of days represented/delivered in
January (or any specified month) 2006 WITHOUT counting January 2nd
2X's?

Just the number of days out of 31 that have shown activity?

 

[Ragdyer]

Say your dates are in A1 to A100.

Enter the month you're looking to count into D1, using the format of "mmm"
(Jan, Feb, Mar, ...etc.).

Then try this *array* formula:

=COUNT(1/FREQUENCY(IF((TEXT(A1:A100,"mmm")=D1)*(A1:A100<>""),MATCH(A1:A100,A
1:A100,0)),ROW(1:100)))

--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.

Note: This will count all unique dates for a particular month, no matter
what the year.!

 

[wx4usa]

RD,

Thanks for the quick reply, however I could not get it to work.

My column A represents each delivery route. I may have 90 delivery
route entries in January, but I may only actually be running trucks on
23 days in January.

There will be many days that are duplicated because I am running 3-4
trucks on that day, but I want excel to count that day as 1.

If I run 1 truck, it should count as 1. If I run multiple trucks, it
should count as 1, if I run no trucks, it should count as 0.

How do I count just the specific days in January 2006 I was delivering?

 

[Ragdyer]

What does "not get it to work" mean?

No answer - Wrong answer - Error message?

The array formula that I suggested will count unique dates entered in Column
A, from A1 to A100, where the date matches the month that is entered into
cell D1 in the 3 letter abbreviated format of the example in my post (Jan -
Feb).

ALSO, the dates must be true XL recognized dates, which also means that
there can be *nothing else* in the cell, except the date ... And the formula
must be entered using CSE (<Ctrl> <Shift> <Enter>).

Be more explicit as to what is "not working"!

 

[Gord Dibben]

What does RD's formula return for you?

Works for me if entered in any cell but D1.

Don't forget the CTRL + SHIFT + ENTER

Enter Jan in D1


Gord Dibben MS Excel MVP

 

[wx4usa]

Here are the only 3 dates I have entered yet in column A. The formula
returns 1.

I can send you the small worksheet to look at. I do have the dates in
a5:a100 and I changed those in the formula you sent me. THe dates
below are in a5:a7 the remainder is blank at this time. I will
eventually have up to 3000 date lines.

I did array enter it too.

1/31/2006
1/30/2006
12/20/2006

 

[Dave Peterson]

I put your dates in A1:A3 and JAN in D1 (not a date, just the letters JAN in
D1).

RD's formula returned 2.

It returned 1 if I didn't hit ctrl-shift-enter, though.