Formula to count # if blank or date in the future

Options

Looking to count the total number of either blank or dates in the future - here is the formula that works to show blanks, but need to add is blank or todays date


=IF(COUNT(CHILDREN()) > 1, SUM(CHILDREN()), COUNTIFS({Site}, BFS$1, {Department}, $Department@row, {VResults}, <>"NOT DETECTED", {RTW Date}, ISBLANK(@cell)))

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @sjmcdonald

    No problem! You should be able to add in an OR Function to identify the two things you're looking for.

    Try this:

    =IF(COUNT(CHILDREN()) > 1, SUM(CHILDREN()), COUNTIFS({Site}, BFS$1, {Department}, $Department@row, {VResults}, <>"NOT DETECTED", {RTW Date}, (OR(ISBLANK(@cell), @cell >= TODAY())))


    Cheers!

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @sjmcdonald

    No problem! You should be able to add in an OR Function to identify the two things you're looking for.

    Try this:

    =IF(COUNT(CHILDREN()) > 1, SUM(CHILDREN()), COUNTIFS({Site}, BFS$1, {Department}, $Department@row, {VResults}, <>"NOT DETECTED", {RTW Date}, (OR(ISBLANK(@cell), @cell >= TODAY())))


    Cheers!

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!