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

Tammy Parker · 04/24/20

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.

Tammy Parker · 04/24/20

Thank you so much!! Worked perfectly!

Tammy Parker · 04/24/20

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"))))

Joanna Waligora · 04/27/20

Hi,

If this is the exact formula you're using then you're missing a bracket between the first instance of IFTODAY() --> IF(TODAY().

The check against the completed field should be an equation. IF (Completed1=0, ... It's 0 for false (unchecked) and 1 for true (checked).

Also, it looks like you're referencing Completed from row 1, but [Completion Target Date] from row 2. If your field is called "Completed" then that's what "Completed1" means. In this formula I would suggest using @row instead of row numbers. It basically references the row being 'currently' calculated and may also offer some processing related benefits since the software doesn't have to rewrite all equations just because a row was inserted somewhere mid-file. So

IF (Completed@row=1, ...)

Try:

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

J.

Joanna Waligora · 04/24/20

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 · 04/24/20