COUNTIFS formula giving incorrect value


My Countifs formula is giving a different value than when I filter on my sheet which is the correct answer.

I am trying to pull the number of team members who were hired before Jan 31st, Termed between Jan 1 and Jan 31, are current employees (no term date), who do not fall into the position code or term code in the formula.

Here is the formula I am using: =COUNTIFS({TermDate}, OR(@cell <= DATE(2024, 1, 31), @cell >= DATE(2024, 1, 1), @cell = ""), {Hiredate}, <=DATE(2024, 1, 31), {Position Code}, OR(@cell <> "HHOPR", @cell <> "HHLAB", @cell <> "HHIW", @cell <> "HHTEAM", @cell <> "HHFM", @cell <> "HHIBEW", @cell <> "HHPD", @cell <> "INTERN", @cell <> "SGMT", @cell <> "Temp2"), {Term Codes}, OR(@cell = "", @cell <> "DNS", @cell <> "DNSI", @cell <> "TRANSFER"))

This gives me 809 instead of 727 which is the correct answer.

Please help! What am I doing wrong


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!