+ Reply to Thread
Results 1 to 5 of 5

Due dates sumif/countif formula questions

  1. #1
    Registered User
    Join Date
    10-17-2014
    Location
    Monticello
    MS-Off Ver
    MS Office 2010
    Posts
    3

    Due dates sumif/countif formula questions

    I have a table -Table2- with a column called -Due Date- for various tasks. On a graph along side I want to have a count of the number of tasks I have due within a certain time frame.

    I'd like to have 4 categories, Due Date: Today, Within 7 days, Within 4 weeks (or 28 days), within 6 months.

    The formula for Today was easy, I entered: =COUNTIF(Table2[Due Date],TODAY())

    I have little experience with formulas and I thought the other categories would be fairly straight forward but I have not been successful. For instance, i found this formula =SUM(IF(Table2[Due Date]>=(TODAY()-WEEKDAY(TODAY(),2)+1),IF(Table2[Due Date]<(TODAY()-WEEKDAY(TODAY(),2)+1+7),1,0),0)) ------- the problem with this one is that it's counting tasks due this calendar week, instead of within 7 days of the current date (example: today it's Friday the 17th, the above formula does not count tasks I have due the following Tuesday, the 21st. It does not count it because next Tuesday is not technically THIS WEEK. But I need the formula to count tasks due within the next 7 days regardless of whether it falls within the same calendar week. This goes for my formula for 'due date within 4 weeks' as well, I'm less worried about tasks due within October than I am about the next 4 weeks irrespective of the calendar month.

    Thank you in advance for your help =)

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Due dates sumif/countif formula questions

    Use COUNTIFS
    Within 7 days,
    =COUNTIFS(Table2[Due Date], ">="&Today()+1, Table2[Due Date], "<="&Today()+7)
    Within 4 weeks (or 28 days),
    Not including the next 7 days
    =COUNTIFS(Table2[Due Date], ">="&Today()+8, Table2[Due Date], "<="& Today()+28)

    within 6 months.(not including next 28 days)
    =COUNTIFS(Table2[Due Date], ">"&Today()+28, Table2[Due Date], "<="& EDATE(TODAY(),6))
    Is that what you wanted?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    10-17-2014
    Location
    Monticello
    MS-Off Ver
    MS Office 2010
    Posts
    3

    Re: Due dates sumif/countif formula questions

    Thanks for writing back. The formula for 'due within 7 days' and 'due within 6 months' seem to work. The formula for 'due within 4 weeks' produced an error I was receiving earlier when I was trying to write these. Instead a producing a single number I get: 1/1/00.

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Due dates sumif/countif formula questions

    Select that cell and change cell format to General.

    If you have errors, it's easier to troubleshoot if you can upload an example sheet (Go Advanced>Manage Attachments)

  5. #5
    Registered User
    Join Date
    10-17-2014
    Location
    Monticello
    MS-Off Ver
    MS Office 2010
    Posts
    3

    Re: Due dates sumif/countif formula questions

    That worked, thank you my question is resolved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How to set formula sumif or countif?
    By wrblee in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 10-24-2012, 12:42 AM
  2. IF/THEN & a SUMIF & COUNTIF Formula
    By dagindi in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-25-2010, 12:32 PM
  3. Replies: 3
    Last Post: 12-13-2009, 11:48 PM
  4. Replies: 5
    Last Post: 07-19-2006, 12:22 PM
  5. Formula questions related to dates
    By blueglass74 in forum Excel General
    Replies: 2
    Last Post: 05-19-2005, 10:28 AM

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