Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Bristol, Avon, England
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Countif for dates between (xp)

    I have a column which contains dates, I want to use the countif function to count the dates in a certain month. I can't seem to get it working right. Can somebody suggest something

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Countif for dates between (xp)

    If the dates are in A1:A25, then the following array formula (hold down Shift+ Ctrl when you enter it) will count the number that are in January:

    <pre>=SUM((MONTH(A1:A25)=1)*(A1:A25<>""))
    </pre>


    To count February, change the "=1" to "=2", or change the "=1" to "=" and a cell reference where you will enter the month number.
    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Countif for dates between (xp)

    I can't help if you are committed to using "countif" - but if you are willing to use an array function, this will work:

    Assuming your column of dates is in column A, extending from A2 to A78, and you want to be able to count the number that occur in May, June, November, etc - place a number from 1-12, representing the month (January - December) in cell A80. In cell B80, enter the formula:
    <pre>=SUM(IF(MONTH(A$2:A$78) = A80,1,0))
    </pre>

    This will be an array formula, and must be entered by hitting ctrl-shift-enter. Excel will insert braces around the formula to indicate that it is an array formula. If you need to see the count for more than one month at a time, drag to copy the formula down the B column, and then put in the months you need. If required, you can have twelve copies of the formula and show the count for all twelve months at once. The referenced range for the formula (A2:A78) can be changed either by editing the formula bar or by dragging an outline - just remember to enter the formula by ctrl-shift-enter.

    An alternate formula woudl be:
    <pre>=SUM((MONTH(A$2:A$78) = A81)*1)
    </pre>

    This might evaluate faster, but I doubt that the difference would be noticeable

    If you are dealing with more than one year you can edit the criteria to use an "AND" function to test for the value in the range to be more than a particular starting date and less than an ending date, as well - unless you want to aggregate May 02 with May 01 <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Countif for dates between (xp)

    As well as the other excellent suggestions, you can use two countifs like this:

    =COUNTIF(daterange,">"&DATE(year,month,0))-COUNTIF(daterange,">"&DATE(year,month + 1,0))
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •