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

Find more posts tagged with

Formulas

functionality

Accepted answers

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.

All comments

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! 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.