Count status of tasks between 2 dates
Hey there! I'm trying to find a way to reference data from one sheet into another, with a formula that will count some metrics to display on my dashboard. I've managed to do it for counting task status for the whole project but am struggling to figure out the formula to narrow down specific tasks between dates.
Previous formula used was: =COUNTIF({TLS (In-House) Ver.6 Range 1}, "Green")
How do I count this between specific dates from that sheet ?
I've included screenshot of the 2 sheets it'd be a great help to know.
Comments
-
First you would need to switch from a COUNTIF to a COUNTIFS to be able to include multiple sets of criteria.
=COUNTIFS({TLS (In-House) Ver.6 Range 1}, "Green")
.
Next you just maintain the syntax pattern of range/criteria/range/criteria to establish everything you need.
=COUNTIFS({TLS (In-House) Ver.6 Range 1}, "Green", {Date Range}, Date Criteria)
-
I've tried this formula but it keeps coming back as incorrect arguement what am I missing?
=COUNTIFS({TLS (In-House) Ver.6 Range 1}, "Green", {TLS (In-House) Ver.6 Range 5}, >=DATE(2019 / 8 / 1), {TLS (In-House) Ver.6 Range 5}, <=DATE(2019 / 8 / 31))
-
Start with fixing your DATE functions. You should be using commas instead of forward slashes.
If that doesn't work, there are a few more things we can try.
-
That worked perfectly thank you!
-
-
I am trying to do something similar - and I am having issues. I am trying to count the number of tasks with a status of "Complete" over a year. I have the following formula that keeps coming back as #UNPARSEABLE...
=COUNTIFS(Status:Status, ("Complete"), (>=DATE(2020, 1, 1), <=DATE(2020, 12, 31)))
-
@Katherine Galindo Try something like this...
=COUNTIFS(Status:Status, "Complete", [Date Column]:[Date Column], IFERROR(YEAR(@cell), 0) = 2020)
-
I'm trying to count phases between two dates. Formula is not counting the dates within the specified range.
=COUNTIFS({CI - Projects Schedule by Phase Range 1}, "OAP3", {CI - Projects Schedule by Phase Range 2}, >=DATE(2021, 1, 1), {CI - Projects Schedule by Phase Range 2}, >=DATE(2021, 1, 31))
-
@Maria McKown You need to flip the second date argument to less than or equal to instead of greater than or equal to.
">="
to
"<="
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!