Counting blank cells when other criteria is met

Hi,

I need to add on top of a formula that counts tasks that are in progress or not started that are past due. The formula below works for that, but now I also need my total to include cells in the target completion date that are blank.

=COUNTIFS(Status129:Status144, OR(@cell = "In Progress", @cell = "Not Started"), [Target Completion Date]129:[Target Completion Date]144, <TODAY())

Any suggestions would be most helpful.

Thank you,

Emily

Tags:

Best Answer

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    Answer ✓

    =COUNTIFS(Status:Status, OR(@cell = "In Progress", @cell = "Not Started"), [Target Completion Date]:[Target Completion Date], <TODAY()) + COUNTIFS(Status:Status, OR(@cell = "In Progress", @cell = "Not Started"), [Target Completion Date]:[Target Completion Date], "")


    =COUNTIFS(Status129:Status144, OR(@cell = "In Progress", @cell = "Not Started"), [Target Completion Date]129:[Target Completion Date]144, <TODAY()) + COUNTIFS(Status129:Status144, OR(@cell = "In Progress", @cell = "Not Started"), [Target Completion Date]129:[Target Completion Date]144, "")

Answers

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    Answer ✓

    =COUNTIFS(Status:Status, OR(@cell = "In Progress", @cell = "Not Started"), [Target Completion Date]:[Target Completion Date], <TODAY()) + COUNTIFS(Status:Status, OR(@cell = "In Progress", @cell = "Not Started"), [Target Completion Date]:[Target Completion Date], "")


    =COUNTIFS(Status129:Status144, OR(@cell = "In Progress", @cell = "Not Started"), [Target Completion Date]129:[Target Completion Date]144, <TODAY()) + COUNTIFS(Status129:Status144, OR(@cell = "In Progress", @cell = "Not Started"), [Target Completion Date]129:[Target Completion Date]144, "")

  • Emily T.
    Emily T. ✭✭✭

    Awesome! Thank you so much. That worked!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!