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

Thank you! That was my problem.

Sarah

• ✭✭✭✭✭✭

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