Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TugboatEng on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

Count or sort by month?

Status
Not open for further replies.

ACtrafficengr

Civil/Environmental
Joined
Jan 5, 2002
Messages
1,641
Location
US
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
 
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
 
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
 
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!
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top