Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count by Date Range

    I have a rather large Excel file (database) that I need to count by quarter. For example: xx number of people called during the 1st Qtr, xx in the second Qtr, etc. I tried using the COUNTIF function, but the criteria for this function doesn't accept a range of dates (or I can't get the syntax correct). I need, for instance, "count this row if 'call date' is between 10/01/2000 and 12/31/2000".

    I am running Excel 97.

    Randall Davis
    Data Management Specialist
    Wichita, KS

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

    Re: Count by Date Range

    The following array formula will count the dates in the range A1:A50 that are in the fourth quarter:

    <pre>=SUM(((A1:A50) >= DATEVALUE("10/1/2000")) * ((A1:A50) <= DATEVALUE("12/31/2000")))
    </pre>


    Since this is an array formula, you must hold down the Shift and Ctrl keys when you press Enter to enter it into a cell.
    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count by Date Range

    Great!! This works, but I don't understand how. I need to study up on Arrays!

    Now, I need to go one step further. Is there a way to automagically calculate the rows that need to be counted. In the formula example you've given 'A1:A50' would change periodically. Is there a way to automate this?

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Count by Date Range

    If you want a year independent date the following formula should work :-

    =SUM(IF(ROUNDUP(MONTH(DateRange)/3,0)=<font color=red>1</font color=red>,1,0))

    where DateRange is the range of your dates to be evaluated (e.g. A1:A100) and the number in red is the quarter you want to count.

    Again, like Legarre's formula, this is an array formula, so once you type it in you should hold Ctrl-Shift whilst you press the Enter key. When you do that Excel will enclose it in {} brackets.

    (The formula =ROUNDUP(MONTH(A1)/3,0) will return the Quarter of the date in A1)

    Andrew C

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

    Re: Count by Date Range

    As long at the range speified covers the maximum range, and the unused cells don't contain dates, then the formula should work. However, it might also be possible to create a range that would vary dynamically if you can tell me how to know how to vary it.
    Legare Coleman

  6. #6
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count by Date Range

    Your formula works if I specify exactly the rows that contain data. If any of the rows 1:50 are blank (for instance, my data ran out on row 49), the answer defaults to 1.

    My data gets added to weekly, and therefore the number of rows grows weekly through the course of our FY. How do I get the range to grow dynamically as the amount of data grows.

    It is organize into a simple database list.

  7. #7
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Re: Count by Date Range

    Hi
    Try this simple function

    Function CalculateQtr(callDate As Date)
    Dim intQtr, intMonth As Integer
    intMonth = Month(callDate)
    Select Case intMonth
    Case 1 To 3
    intQtr = 1
    Case 4 To 6
    intQtr = 2
    Case 7 To 9
    intQtr = 3
    Case Else
    intQtr = 4
    End Select
    CalculateQtr = intQtr
    End Function

    If you put this in the personal workbook and then use the f(x) tool to apply it you will see a formula like this
    =CalculateQtr(b2).
    This assumes that b2 contains a call date.
    Then sort data by CalculateQtr rsults, use the DATA|SuBtotal facility to and count at each change in Qtr et voila.
    Good luck
    Cheers
    Geof

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Count by Date Range

    If you are referring to the formula I posted above, I am sorry that I did not reply sooner, but thought you were referring to Legarre's formula.

    I should have mentioned that formula would treat blank cells as 1/1/1900 (Month(0) = 1 in Excel), and hence evaluate to Qtr 1. The following is an amended version which should work for blanks, but not for values that are not dates, even spaces.
    <pre> =SUM(IF(IF(A1:A50<>0,ROUNDUP(MONTH(A1:A50)/3,0))=1,1,0))</pre>

    sorry again.

    Andrew C

  9. #9
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count by Date Range

    Oops. As I would make changes to the formula, I forgot to make it an array. Your formula does work as designed. Thanks for everyone's help. My first post to the Lounge was a rousing success!!

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

    Re: Count by Date Range

    I tested my formula in both Excel 97 and Excel 2000, and it worked fine if I specified the complete range but did not have dates in all cells in the range. Are you sure that you held Shift+Ctl when you entered the foumula? What you describe is what would happen if you did not, and the first cell in the range was in the quarter being tested for. In any event, the following formula should test only the cells that contain something in the range A1:A50, as long as there are no cells containing data after the first empty cell in the range.

    <pre>=SUM(((A1:OFFSET(A1,MAX(1,COUNTA(A1:A50))-1,0,1,1)>=DATEVALUE("1/10/2000")))*((A1:OFFSET(A1,MAX(1,COUNTA(A1:A50))-1,0,1,1))<=DATEVALUE("31/12/2000")))
    </pre>



    Sorry if you have to scroll to the right to see all of that.


    *** Geoff W. I removed the "pre" tags- with long lines, they cause problems with viewing ***
    *** Legare Coleman: I put the pre tags back in. Not having them causes problems with symbols in those long lines getting changed by the board software ***
    Legare Coleman

Posting Permissions

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