Multi-Layer Formula for COUNTIFS and IF

Maria Watters

Hello,

I am trying to see the number of employees that called off for each shift for yesterday only. This is what I have so far. I want to see the number of employees that called off on "Shift A" and that call off is "Unscheduled" , but ONLY want to see any calls with the call-off date that was yesterday.

Do I need an "And" in there somewhere?

=COUNTIFS({CMHIP Employee Call Off Log Range 1}, "Shift A", {CMHIP Employee Call Off Log Range 2}, "Unscheduled", IF({CMHIP Employee Call Off Log Range 3}@row = TODAY(-1)))

Paul Newcome

=COUNTIFS({CMHIP Employee Call Off Log Range 1}, "Shift A", {CMHIP Employee Call Off Log Range 2}, "Unscheduled", {CMHIP Employee Call Off Log Range 3}, @cell = TODAY(-1))

Give that a go.

Paul Newcome

=COUNTIFS({CMHIP Employee Call Off Log Range 1}, "Shift A", {CMHIP Employee Call Off Log Range 2}, "Unscheduled", {CMHIP Employee Call Off Log Range 3}, @cell = TODAY(-1))

Give that a go.

Maria Watters

Thank you! That worked

Paul Newcome

Happy to help! 👍️

Maria Watters

Hello @Paul Newcome I do have another questions on this...Sorry!

If now I want to see the dates for the prior three days (ex. on Monday, I want to see the data for Friday, Saturday, and Sunday) How would that be done? if I change the date from (-1) to (-3) that would only show Friday, correct?

=COUNTIFS({CMHIP Employee Call Off Log Range 1}, "Shift A", {CMHIP Employee Call Off Log Range 2}, "Scheduled", {CMHIP Employee Call Off Log Range 3}, @cell = TODAY(-3))

Paul Newcome

Correct. We would use an AND statement to say greater than 3 days ago but less than today.

=COUNTIFS({CMHIP Employee Call Off Log Range 1}, "Shift A", {CMHIP Employee Call Off Log Range 2}, "Scheduled", {CMHIP Employee Call Off Log Range 3}, AND(@cell >= TODAY(-3), @cell <= TODAY()))

Maria Watters

Thank you so much!

Paul Newcome

Happy to help! 👍️