Count or sort by month?

[ACtrafficengr]

Given a list of dates in different years, is there a way to count how many of the dates are in January, for example? I tried various things, like formating the cells to show justthe month and using "countif", but none worked.

------------------------------------------
"...students of traffic are beginning to realize the false economy of mechanically controlled traffic, and hand work by trained officers will again prevail."

Wm. Phelps Eno, ca. 1928

 

[handleman]

Assuming your list in column A, you need a second column, B in this example, like below:

A B
6/5/2006 6
4/3/2005 4
5/6/2007 5
6/3/2002 6
12/15/1999 12

The second column formula in each cell is:

=MONTH(A1)

Then you can sort, use countif, etc on column B. You can hide B, or if necessary put it on another sheet (not another workbook).

-Josh

 

[pmover]

try experimenting with the autofilter (Data=> Filter=> AutoFilter) capabilities of excel. a row with headings is helpful before starting the filtering. the help system should be self explanatory.

good luck!
-pmover

 

[onlyadrafter]

How about

=SUMPRODUCT((MONTH(A1:A6))*(MONTH(A1:A6)=1))

the ranges must be the same in both parts.

----------------------------------
Hope this helps.
----------------------------------

maybe only a drafter
but the best user at this company!

 

[joerd]

Onlyadrafter,

That gives you the number of the month times its count, instead of the count. This works fine for January (because it is month number 1 ;-) ) but not for other months. Your formula should be:
[tt]=SUMPRODUCT(1*(MONTH(A1:A6)=6))[/tt] where 6 is the number of the month you're interested in (in this case June).


Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.