Return a count based on date range from reference sheet

errank

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.

Kelly Moore

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

Hey

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

Kelly

Kelly Moore

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

Thank you so much Kelly, That worked!

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

Hey

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

Kelly