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.

smartsheet community.png

Find more posts tagged with

Formulas

Comments

Paul Newcome

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)

siofra.keane98086

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))

Paul Newcome

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.

siofra.keane98086

That worked perfectly thank you!

Paul Newcome

Happy to help!

Katherine Galindo

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)))

Paul Newcome

@Katherine Galindo Try something like this...

=COUNTIFS(Status:Status, "Complete", [Date Column]:[Date Column], IFERROR(YEAR(@cell), 0) = 2020)

Maria McKown

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))

Paul Newcome

@Maria McKown You need to flip the second date argument to less than or equal to instead of greater than or equal to.

">="

"<="

Sign in to join the conversation!

Quick Links

Trending discussions

Join distinct collect with contains
Can someone help with this formula? Its not working as-is, but I also would like to use the "contains"/"has" for the leader column as I combined a bunch of contact columns. Here is my attempt to use has - Heres the references (other sheet) Month, all Leads, and R&B prog is the first one (project)
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.
Nested IF Statements
Hello. I cannot figure this out. =IF([GL Code]@row = 14030, "Leasehold") How do I add additional IF statements in the same cell, for example if the GL Code@row = 14040, "FF&E", GL Code@row = 14050, "CR Equip" Thanks.