Welcome!

Count if Date is Past Due

I need a formula to count the number of tasks past due per person. I tried a Count Ifs formula and then I also tried using a checkbox.

So in the example attached, I want to count all the overdue items in 1 Rough Draft Writing Due Date column per person.

Count overdue items.JPG

Find more posts tagged with

Comments

Kimberlee Adam

Never mind, I got it figured out. Just in case anyone else has the same question. Here is the formula.

=IF(AND([1 Rough Draft Writing Due Date]11 < TODAY(), NOT([1 Rough Draft Writing Status]11 = "Completed")), 1, 0)

Now, I do have to figure out how to not count it if the date cell is blank.

Sarah_lee123

Hello there, Did you ever figure out the rest of your formula?

Thanks

Paul Newcome

@Sarah_lee123 There are a few options to accomplish the above. Using the checkbox column and then counting that would look something along the lines of...

=IF(AND([1 Rough Draft Writing Due Date]@row <> "", [1 Rough Draft Writing Due Date]@row < TODAY(), [1 Rough Draft Writing Status]@row <> "Completed"), 1)

and then to get the count...

=COUNTIFS([Checkbox Column]:[Checkbox Column], 1, [Name Column]:[Name Column], "John Doe")

Or you could skip the checkbox column and go directly to the count with:

=COUNTIFS([1 Rough Draft Writing Due Date]:[1 Rough Draft Writing Due Date], AND(@cell <> "", @cell < TODAY()), [1 Rough Draft Writing Status]:[1 Rough Draft Writing Status], @cell <> "Complete", [Name Column]:[Name Column], "John Doe")

Gamado

What about Past due tasks -the dates are in the past and the checkbox is unchecked. This is for columns and not rows? @Paul Newcome

Paul Newcome

@Gamado I'm not sure what you're asking with that last bit about columns vs rows.

Gamado

@Paul Newcome I am trying to countif a column for past due tasks and a column with uncheckedbox. how do I go about it?

thanks,

Paul Newcome

@Gamado You would use a straightforward COUNTIFS for this.

=COUNTIFS(range 1, criteria 1, range 2, criteria 2)

Gamado

@Paul Newcome

=COUNTIFS([Target Date]:[Target Date], <TODAY(), Done:Done = 0)

this formula is not work.

Paul Newcome

@Gamado You need a comma between the second range and the second criteria.

Sign in to join the conversation!

Quick Links

Trending discussions

Not enough space in the cell for my notes
Hi, I'm using smartsheet to track updates on each task in a tracker. These updates may span over a year, so they get lengthy. I was excited about smartsheets because viewing these notes is a lot cleaner. However, I've just realized that there is a content limit to a cell, which is a huge issue. While excel was annoying to…
how can i overlap 2 tasks.
Hello, i've a question about 2 tasks. Task 1 has for example 4 hours and should be on day 1 for example 6 january. Task 2 has 30 hours and should be on start on 4 january and futher. How can i manage that on 6 january task 2 has only 4 hours? Now i got the message user is over-allocated because task 2 is planned on a full…
Update Automations
Hi All, We have several update automations on various solutions - and have worked very well for getting info from people that dont have smartsheet access - how ever in Sept we switched over to SSO and MFA and activated the Safe Share - We have now run into an issue that when our update automations trigger and people within…