Anyone able to help figure out this formula?

Nat

I would very much appreciate anyone's help on this. I have moved the brackets around and changed anything I can think of, but it's coming up "unparseable". The column headers and sheet references are right.

=IF(TODAY() >= [Quarter Start]@row, COUNTIFS({HR mock-up sheet START DATE}, @cell <= [Quarter End]@row, {HR mock-up sheet DATE OF DEPARTURE}, OR(@cell = "", @cell >= [Quarter Start]@row, AND({HR mock-up sheet JOB TYPE}, “Permanent FT”))))

ie: only including past information, for this current quarter- as long as the employee's start date is in the past and departure date is after the beginning of this quarter (or empty) - count how many rows in JOB TYPE are "Permanent PT"

Nick Korna

That's some progress I guess! Next up:

The OR statement isn't closed off and I'm not sure you need the AND portion either as you've only a single expression in there. Something along the lines of:

=IF(TODAY() >= [Quarter Start]@row, COUNTIFS({HR mock-up sheet START DATE}, @cell <= [Quarter End]@row, {HR mock-up sheet DATE OF DEPARTURE}, OR(@cell = "", @cell >= [Quarter Start]@row), {HR mock-up sheet JOB TYPE}, “Permanent FT”)))

Nick Korna

One thing instantly leaps out - the quotation marks around Permanent FT are curved rather than straight. Change the “Permanent FT” to "Permanent FT" .

Try changing this over and see what the result is as the different quotation marks will break things.

Nat

@Nick Korna good catch! That was definitely an issue... now I get "invalid operation", so my problems are worse than I thought.

Nick Korna

That's some progress I guess! Next up:

The OR statement isn't closed off and I'm not sure you need the AND portion either as you've only a single expression in there. Something along the lines of:

=IF(TODAY() >= [Quarter Start]@row, COUNTIFS({HR mock-up sheet START DATE}, @cell <= [Quarter End]@row, {HR mock-up sheet DATE OF DEPARTURE}, OR(@cell = "", @cell >= [Quarter Start]@row), {HR mock-up sheet JOB TYPE}, “Permanent FT”)))

Nat

@Nick Korna that was it! I closed the bracket on the OR statement - I had already taken out the AND- it works!

Thanks so much for taking the time!

Nick Korna

No problem, glad it is all fixed for you now! ☺️