Summary Function with Date Range

Sarah Shafer

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)

Sarah Shafer

Thank you! That was my problem.

Sarah

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