+ Reply to Thread
Results 1 to 10 of 10

Counting Tasks With Due Dates between TODAY and TODAY+7

  1. #1
    Registered User
    Join Date
    04-28-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    8

    Counting Tasks With Due Dates between TODAY and TODAY+7

    Forgive my inexperience. I have a spreadsheet that lists several tasks with several different estimated completion dates having individual people as the task owners. I would like to have a formula in a cell that displays the total number of tasks due between today's date and a week from today's date that updates when each person filters on their own name as task owner.
    ExcelHelp.xlsx
    The number of rows varies in the tens or hundreds, so a solution that includes $B:$B would be helpful.
    Attached Images Attached Images
    Last edited by Erik_with_a_K; 04-28-2013 at 05:58 PM. Reason: Replaced screencap of workbook with workbook

  2. #2
    Valued Forum Contributor Lemice's Avatar
    Join Date
    04-13-2013
    Location
    Somewhere.
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Counting Tasks With Due Dates between TODAY and TODAY+7

    Hello,

    This formula might the what you are looking for
    Please Login or Register  to view this content.
    This will count the number of tasks (including duplicated) between TODAY() and a week after.

    If somehow you want to sum the number on the task column however, it will be
    Please Login or Register  to view this content.
    (copy pasta from Ford)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools

    Regards,
    Lem

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,901

    Re: Counting Tasks With Due Dates between TODAY and TODAY+7

    Hi and welcome to the forum

    Take a look at the countifS() function for what you want, something like
    =countifs(date_range,">="&today(),date_range,"<="&today()+7)
    you can add additional croteria as needed

    Also, not all members can open picture files (company fire walls and stuff), so please upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    04-28-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Counting Tasks With Due Dates between TODAY and TODAY+7

    Thanks for the replies. Lem, your solution works fine except that the B range will vary on a daily basis; this is why I suggested using $B:$B. When I amend your formula using $B:$B, it works except when column C is filtered for a particular task owner, e.g. "David". The total remains at 8, rather than adjusting to the number of filtered cells. Does this make sense?
    FDibbins, per your instructions, I will try to edit the original post to include a workbook sample rather than a picture.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,901

    Re: Counting Tasks With Due Dates between TODAY and TODAY+7

    Quote Originally Posted by FDibbins View Post
    =countifs(date_range,">="&today(),date_range,"<="&today()+7)
    you can add additional croteria as needed
    Thats why I added that last part. You can nclude an extra criteria for ,name_range,"David"

  6. #6
    Registered User
    Join Date
    04-28-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Counting Tasks With Due Dates between TODAY and TODAY+7

    I can basically understand the concept of adding criteria as needed within the countifs function; however, I think I am needing something more along the lines of a SUMPRODUCT array formula? Something that has the bones of
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    . I am wandering in over my head, because I don't understand arrays or how to adapt this formula to my needs.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,901

    Re: Counting Tasks With Due Dates between TODAY and TODAY+7

    In most cases, sumproduct can be replaced with formulas like sumifS() and countifS(), and this is 1 of those times.

    =COUNTIFS($B:$B,">"&TODAY(),$B:$B,"<="&TODAY()+7,$C:$C,"Tom")

    "Tom" can be hard-coded like I did there, or you can put the name in a cell and reference it

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Counting Tasks With Due Dates between TODAY and TODAY+7

    Try this...

    =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2,ROW(B2:B100)-ROW(B2),0)),--(B2:B100>=TODAY()),--(B2:B100<=TODAY()+7))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  9. #9
    Registered User
    Join Date
    04-28-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Counting Tasks With Due Dates between TODAY and TODAY+7

    Quote Originally Posted by Tony Valko View Post
    Try this...

    =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2,ROW(B2:B100)-ROW(B2),0)),--(B2:B100>=TODAY()),--(B2:B100<=TODAY()+7))
    This, essentially, is what I am after. Is there any way to do this without specifying an exact row range? The row numbers could be anywhere from 600-700; it changes daily. Would it just be best to put the row range at something like (B2:B5000) just to make sure?
    Thanks to all for your advice.

  10. #10
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Counting Tasks With Due Dates between TODAY and TODAY+7

    Just make the range big enough to include all the data.

    If you expect the maximum number of rows of data to be ~700 then use B2:B800 as the range. You don't want to waste resources by having to calculate a boatload of empty cells!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1