Return a count based on date range from reference sheet

I have created a summary sheet to get a count of how many times "Foundation/Foundational Analytics/Health Insights/Type 1 Audit" shows up in a column, referencing another sheet using this formula..

"=COUNTIF({Charter WAE AS-S Action Item List Range 1}, "Foundation/Foundational Analytics/Health Insights/Type 1 Audit")

In that same reference sheet, I also have two columns names "start date" and "completion date".

I would like now to edit the formula above to only return a specific count based on a date range, say between 10/1/22 and 12/6/22. I have tried several variations including the COUNTIFS and cannot get it to work correctly. Please help! Thank you.

Best Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @errank

    =COUNTIFS({Charter WAE AS-S Action Item List Range 1}, "Foundation/Foundational Analytics/Health Insights/Type 1 Audit", {Charter WAE AS-S Action Item List Start Date}, @cell>=DATE(2022,10,1), {Charter WAE AS-S Action Item List End Date}, @cell<=DATE(2022, 12,6)

    Please remember since this formula contains cross sheet references that you must manually create these references from within the formula window - you cannot simply copy paste this formula into your sheet.

    Will this work for you?

    Kelly

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey

    There’s a missing parenthesis to close your Countifs, but otherwise it looks exactly as you wrote

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @errank

    =COUNTIFS({Charter WAE AS-S Action Item List Range 1}, "Foundation/Foundational Analytics/Health Insights/Type 1 Audit", {Charter WAE AS-S Action Item List Start Date}, @cell>=DATE(2022,10,1), {Charter WAE AS-S Action Item List End Date}, @cell<=DATE(2022, 12,6)

    Please remember since this formula contains cross sheet references that you must manually create these references from within the formula window - you cannot simply copy paste this formula into your sheet.

    Will this work for you?

    Kelly

  • errank
    errank ✭✭

    Thank you so much Kelly, That worked!

  • errank
    errank ✭✭

    @Kelly Moore one last question...

    If I wanted to disregard the end date and just use the start date a reference between a certain date range what would that look like?

    =COUNTIFS({Charter WAE AS-S Action Item List Range 1}, "Foundation/Foundational Analytics/Health Insights/Type 1 Audit", {Charter WAE AS-S Action Item List Start Date}, @cell>=DATE(2022,10,1), {Charter WAE AS-S Action Item List Start Date}, @cell<=DATE(2022, 12,6)

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey

    There’s a missing parenthesis to close your Countifs, but otherwise it looks exactly as you wrote

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!