COUNTIFS formula giving incorrect value

Annali

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

Pat Canning

Instead of: OR(@cell <= DATE(2024, 1, 31), @cell >= DATE(2024, 1, 1), @cell = "")

Use: OR(AND(@cell <= DATE(2024, 1, 31), @cell >= DATE(2024, 1, 1), @cell = "")

Pat Canning

Should that first OR statement with the TermDate DATE() functions be an AND statement instead?

<= 1/31/24 OR >= 1/1/24 equates to ALL dates.

Annali

Problem Is that I need to count within the date range and blanks - the AND statement works for the date range but not to count the additional blank cells

Pat Canning

Instead of: OR(@cell <= DATE(2024, 1, 31), @cell >= DATE(2024, 1, 1), @cell = "")

Use: OR(AND(@cell <= DATE(2024, 1, 31), @cell >= DATE(2024, 1, 1), @cell = "")