Countifs Date range if/then question

Ryon
Ryon ✭✭
edited 07/22/22 in Smartsheet Basics

I am trying to count the instances that the final approvals were NOT late. The late flag pops if Final is past Target. The issue I am running into is that I am getting a false result due to the blanks where the final approval has not been given. Is there a way to count the blanks "" in the Final Approval Late? column but take not count them if the Final Approval On date is blank as well?

I thought this would work as a starting point, but I get zero as an answer for everything and that is not correct.

=COUNTIFS({RBI Deviations Tracker Range 1}, [Primary Column]@row, {RBI Deviations Tracker Range 2}, [Column 1]@row, {RBI Deviations Tracker Range 5}, <={RBI Deviations Tracker Range 6})

Thank you!

Answers

  • @Ryon You can probably do it in 2 steps. Use "Final Approval Late?" column to denote if the approval is late or not. In that column use an if formula with below conditions:

    If "Final Approval On" is blank then

    check if Target date is already passed (less than today()) then

    mark "late"

    else

    leave blank

    else

    if "Final Approval On" date is less than target date then

    leave blank (not late)

    else

    mark "late"


    Now count instances of "late" on "Final Approval Late?" column to identify late approval instances.

    Regards,

    Saurabh Maheshwari

    Smartsheet Superstar

    Portfolio Lead, TCS

  • Ryon
    Ryon ✭✭

    Thank you, that worked once I had a bit more explanation from a teammate. It seems that I have a lot more to learn than I thought haha!