Countif Formula Help!

Kate123

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!

Lucas Rayala

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

Thanks, Lucas! This worked great!

Lucas Rayala

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

Thanks, Lucas! This worked great!