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
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!