Summary Function with Date Range

I am attempting to create a sheet summary row function that will count a response of "Staff is Non-compliant" in the "Reason Not Reported" column for a specific date range (the week prior to the reporting week).

My goal is to be able to display the number of non-compliant for the current week in a dashboard.

I have tried the following formula, and multiple variations, but am not able to get the function to work.

Any and all assistance would be greatly appreciated!

=COUNTIF([Reason Not Tested]:[Reason Not Tested], "Staff is Non-compliant" "&[Week Start Date]:[Week Start Date], DATE(>=TODAY-11)&[Week Start Date]:[Week Start Date],DATE(<=TODAY)

Find more posts tagged with

Accepted answers

Sarah Shafer

Thank you! That was my problem.

Sarah

All comments

Heather Duff

Hi @Sarah Shafer ,

Let's try a COUNTIFS formula, with a little different approach to the dates:

=COUNTIFS([Reason not tested]:[Reason not tested], "Staff is non-compliant", [Week Start Date]:[Week Start Date], WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()), [Week Start Date]:[Week Start Date], YEAR(@cell) = YEAR(TODAY()))

This translates to:

Count the number of rows with ALL of the following true:

Reason not tested = Staff is non-compliant
Weeknumber for the Week Start Date is the same weeknumber as today
Year for the Week Start Date is the same year as today

Note that apparently (at least in my account) the "weeknumber" starts on a Monday. So if you have a week start date that is on Sunday, February 6, 2022, it is not considered the same week as today.

Hope this helps. Let me know if it works!

Best,

Heather

Sarah Shafer

Thank you! That was my problem.

Sarah

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.