# Multi-Layer Formula for COUNTIFS and IF

Options
✭✭✭✭✭

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)))

Tags:

• ✭✭✭✭✭
Options

Thank you! That worked

• ✭✭✭✭✭✭
Options

Happy to help! 👍️

• ✭✭✭✭✭
Options

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))

• ✭✭✭✭✭✭
Options

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()))

• ✭✭✭✭✭
Options

Thank you so much!

• ✭✭✭✭✭✭
Options

Happy to help! 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!