Countif Formula Help!

Hello

I have a formula at the moment that is calculating the tenure based on different ranges. The below is example is for those with tenure between 1 and 5 years:

=COUNTIF({GOAL C8 Participant Tracker Range 1}, ">1") - COUNTIF({GOAL C8 Participant Tracker Range 1}, ">5")

However, I want to add another criteria to this formula so that it will only count those who have an "Active" or "On Leave" status in the reference sheet.

I can't get this to work. Any suggestions?

Thanks in advance!

Best Answers

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    edited 02/28/23 Answer ✓

    hi @Kate123 , you need a countifs formula with an "OR" variable. Try this:

    =COUNTIFS({GOAL C8 Participant Tracker Range 1}, ">1", {New Ref Column}, OR(@cell="Active", @cell="On Leave")) - COUNTIFS({GOAL C8 Participant Tracker Range 1}, ">5",{New Ref Column}, OR(@cell="Active", @cell="On Leave"))

    The "{New Ref Column}" is the column containing the "Active" or "On Leave" criteria.

  • Kate123
    Kate123 ✭✭✭✭
    Answer ✓

    Thanks, Lucas! This worked great!

Answers

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    edited 02/28/23 Answer ✓

    hi @Kate123 , you need a countifs formula with an "OR" variable. Try this:

    =COUNTIFS({GOAL C8 Participant Tracker Range 1}, ">1", {New Ref Column}, OR(@cell="Active", @cell="On Leave")) - COUNTIFS({GOAL C8 Participant Tracker Range 1}, ">5",{New Ref Column}, OR(@cell="Active", @cell="On Leave"))

    The "{New Ref Column}" is the column containing the "Active" or "On Leave" criteria.

  • Kate123
    Kate123 ✭✭✭✭
    Answer ✓

    Thanks, Lucas! This worked great!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!