Need to count tasks with due date past due AND complete box not checked

I am new to smartsheet and trying to create a master summary sheet referencing a huge sheet with 1600 files. I need to count "by file name" how many tasks are 1. late < 1 week

2. late >1 week < 3 months

3. late >3 months

There are columns for FILE NAME, Due Date, and a Box to check when completed.

So I need to say if this file name due date is past date < 1 week AND complete Box is NOT checked

There are several for each file name so I assume I will match on the entire column as my reference

=COUNTIFS({Off sheet file name},"ABC", then I need to match on same off sheet due date and checked box.

Hope this makes sense.

Best Answers

  • Tammy Parker
    Tammy Parker ✭✭
    Answer ✓

    Thank you so much!! Worked perfectly!

  • Tammy Parker
    Tammy Parker ✭✭
    Answer ✓

    Hello again.. What if I need to ADD if the complete box is not checked...

    I tried this, but it did not work....

    =IF(Completed1,0, IFTODAY() - [Target Completion Date]2 > 90, "Over 90 Days", IF(TODAY() - [Target Completion Date]2 > 7, "Over A Week", IF(TODAY() - [Target Completion Date]2 > 0, "Less Than One Week"))))

Answers

  • Hi,

    This is not completely baked, but the idea should be sound...assuming I understood the question.

    For simplicity I would do all the base calculations in the source sheet. You can hide these helper columns if needed.

    New column called "Status":

    = IF(Box@row=1,"Done", 

    IF(TODAY()-[Due Date]@row > 90, "Late3", 

    IF(TODAY()-[Due Date]@row > 7, "Late2",

    IF(TODAY()-[Due Date]@row > 0, "Late1", 

    "In Progress"

     ))))

    New column called "Late3PerFile":

    = COUNTIFS([FILE NAME]:[FILE NAME], [FILE NAME]@row, [Status]:[Status], "Late3")

    Replicate with additional columns for Late2 and Late1 options. This aggregates the count of each Lateness category per FILE NAME. So if FILE NAME X appears in 10 rows and 5 of these rows have status of "Late2", then the "Late2PerFile" column will show the number 5 in all 10 rows where FILE NAME X appears.

    If you want the status counts for all 3 lateness categories comma-separated in the same column rather than distributed across 3 dedicated columns, look at the COLLECT function.

    That's it for the easy part :)

    Now, if your summary sheet needs to contain a distinct list of File Names, that will likely be your biggest headache. A functional solution to that problem was provided here: https://community.smartsheet.com/discussion/57396/generate-list-of-unique-values-index-distinct-vlookup

    Once you have the unique list generated, adding the lateness values should just be a simple lookup...the link above references an index/match method for it.

    Good luck?... :)

  • Tammy Parker
    Tammy Parker ✭✭
    Answer ✓

    Thank you so much!! Worked perfectly!

  • Tammy Parker
    Tammy Parker ✭✭
    Answer ✓

    Hello again.. What if I need to ADD if the complete box is not checked...

    I tried this, but it did not work....

    =IF(Completed1,0, IFTODAY() - [Target Completion Date]2 > 90, "Over 90 Days", IF(TODAY() - [Target Completion Date]2 > 7, "Over A Week", IF(TODAY() - [Target Completion Date]2 > 0, "Less Than One Week"))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!