Can someone help me count tasks coming due (using both original & revised due date columns)?

Kimberly K

Hello, fellow Community members! I'm new to formulas/equations in smartsheet and could use your help. I'm trying to create a formula that counts how many actions are due in the next 7 days. The tricky bit for me is that there's both an "Actions Planned Due Date (Original)" column and a "Actions Planned Due Date (Revised)" column. How do I count actions due in the next 7 days, using the revised date column only if it has a date in it, or the original date column if it does not. Thanks for your help!

Paul Newcome

The easiest way would be to insert a helper column on the source sheet that pulling in the appropriate date for every row.

=IF([Revised Date]@row <> "", [Revised Date]@row, [Original Date]@row)

Then you would reference this helper column in your COUNTIFS.

Kimberly K

Thank you, Paul. That worked! :-)

Kimberly K

Sorry to ask again... how would I also include an "and" statement that indicates I only want to count the record if the task status is also "open"?

Paul Newcome

The COUNTIFS basically already has an AND function built in. Just follow the proper syntax, and you should be ok.

=COUNTIFS({1st criteria range}, 1st criteria, {2nd criteria range}, 2nd criteria, ...........................................)

Kimberly K

Thank you!

Paul Newcome

Happy to help. 👍️