×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Count or sort by month?

Count or sort by month?

Count or sort by month?

(OP)
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

RE: Count or sort by month?

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

RE: Count or sort by month?

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

RE: Count or sort by month?

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!

RE: Count or sort by month?

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 winky smile ) but not for other months. Your formula should be:
=SUMPRODUCT(1*(MONTH(A1:A6)=6)) 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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources