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)

Best Answer


  • Heather Duff
    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!



  • Sarah Shafer
    Sarah Shafer ✭✭
    Answer ✓

    Thank you! That was my problem.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!