Count status of tasks between 2 dates

Welcome to the New Smartsheet Online Community


You’ll notice that things have changed a bit. If you need help getting oriented, please take a look at the posts here in the Welcome to the Community category.

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

Tagged:

Comments

  • Paul NewcomePaul 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)

  • 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 NewcomePaul 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.

  • That worked perfectly thank you!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! yes

  • 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 NewcomePaul Newcome ✭✭✭✭✭

    @Katherine Galindo Try something like this...


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

Sign In or Register to comment.