Count status of tasks between 2 dates

siofra.keane98086

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. 

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.

">="

to

"<="