Countif referencing a sheet

I'm trying to reference a column in a sheet if it is less than today's date and is marked as completed. How would i do that? I keep getting error messages. Here's what I have:

=COUNTIF({Findings List Range 1}, >TODAY(), {Findings List Range 2='not completed'})

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    Hi @Pete Cardenas ,

    Because you have multiple criteria you need to use the COUNTIFS function. The syntax is: COUNTIFS( range1, criterion1, [ range2​,criterion2,​... ])

    Your formula would be:

    =COUNTIFS({Findings List Range 1}, <TODAY(), {Findings List Range 2}, "not completed")

    You say it is marked as "not completed". If the cell has that text in it then the formula above is correct. If the cell is a checkbox the the last piece of the formula is {Findings List Range 2}, =0). Checkboxes = 1 when checked and 0 when unchecked.

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    Hi @Pete Cardenas ,

    Because you have multiple criteria you need to use the COUNTIFS function. The syntax is: COUNTIFS( range1, criterion1, [ range2​,criterion2,​... ])

    Your formula would be:

    =COUNTIFS({Findings List Range 1}, <TODAY(), {Findings List Range 2}, "not completed")

    You say it is marked as "not completed". If the cell has that text in it then the formula above is correct. If the cell is a checkbox the the last piece of the formula is {Findings List Range 2}, =0). Checkboxes = 1 when checked and 0 when unchecked.

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!