Multi-Layer Formula for COUNTIFS and IF
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)))
Best Answer
-
=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.
Answers
-
=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.
-
Thank you! That worked
-
Happy to help! 👍️
-
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))
-
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()))
-
Thank you so much!
-
Happy to help! 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- 10.8K Get Help
- 65 Global Discussions
- 69 Industry Talk
- 385 Announcements
- 3.6K Ideas & Feature Requests
- 56 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 464 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
Check out the Formula Handbook template!