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
-
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
-
Hey
There’s a missing parenthesis to close your Countifs, but otherwise it looks exactly as you wrote
Kelly
Answers
-
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
-
Thank you so much Kelly, That worked!
-
@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)
-
Hey
There’s a missing parenthesis to close your Countifs, but otherwise it looks exactly as you wrote
Kelly
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!