Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    May 2002
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count within a date range (Excel 2000)

    Hello,

    This seems like it should be so simple, but I am in need of assistance. I need to be able to count the number of times a group performs a step within a particular date range. The date range is a variable, typed in at the top of the page by a user.

    The attached spreadsheet provides a small sample of the data I am working with. The blue dates are what the user will enter, and will change with each use. The yellow cells are where I need to show how many times a Board opened a file, and how many times they closed a file - within the particular date range.

    I appreciate any guidance!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count within a date range (Excel 2000)

    In cell F3, enter this formula:

    =SUM(($B$3:$B$14=$E3)*($A$3:$A$14>=$B$1)*($A$3:$A$ 14<=$C$1))

    It is an array formula, i.e. confirm with Ctrl+Shift+Enter instead of just Enter. In cell G3, enter this formula:

    =SUM(($B$3:$B$14=$E3)*($C$3:$C$14>=$B$1)*($C$3:$C$ 14<=$C$1))

    also as an array formula (Ctrl+Shift+Enter). Then, select F3:G3 and fill down to row 5. You can make the formulas more readable by defining and using named ranges; see attached version. You'd get formulas such as

    =SUM((Responsible_Board=$E3)*(Open_Date>=Start_Dat e)*(Open_Date<=End_Date))

  3. #3
    Lounger
    Join Date
    May 2002
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count within a date range (Excel 2000)

    Thank you so much, Hans.

    That works perfectly!
    Mary

Posting Permissions

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