COUNTIFS between dates & criteria.

07/24/18 Edited 12/09/19

Greetings Smartsheet Community,    smiley

I am attempting to cross reference another sheet and Count all the Completed tasks within a certain time frame

 

So far I am successful counting the dates in the range using the following formula:

=COUNTIFS({Range 1}, <=DATE(2018, 4, 31), {Range 1}, >=DATE(2018, 4, 1))

 

Next I'd like to add "AND" and "OR" ( to count all tasks "Completed") but I keep running into errors. 

 

Does anyone have a solution?

Any advice would be greatly appreciated.

 

Thank you!

 

Josh

Previous1

Comments

  • L_123L_123 ✭✭✭✭✭

    A:A = Range of dates

    B:B = Range of completion

     

    =Count(collect(A:A,A:A,or(@cell&lt;= Date(2018,4,31),@cell >= Date(2018,4,1)),B:B,"Completed"))

  • edited 07/25/18

    Thanks Luke, I figured it out.

    Here's What I did:

    =COUNTIFS({Start Date}, <=DATE(2018, 5, 31), {Start Date}, >=DATE(2018, 5, 1), {Stats}, "Completed")

    For Completed

    and 

    =COUNTIFS({Start Date}, <=DATE(2018, 5, 31), {Start Date}, >=DATE(2018, 5, 1), {Stats}, <>"Completed")

     

    For Not Completed

     

  • Kelly GabelKelly Gabel ✭✭✭✭✭

    Trying to use a similar formula and coming back with the wrong count. Can someone help me understand what's wrong?

    =COUNTIFS({Estimated Mech Completion Date 1}, <=DATE(20, 10, 1), {Estimated Mech Completion Date 1}, >=DATE(20, 12, 31))


    That's the exact formula I'm using. I'm trying to get a count of all rows in this range where the date is between 10/1/2020 and 12/31/2020. My "year" portion of the formula isn't 4 digits because my source sheet isn't using the 4 digit year...but I have tried it both ways with no luck.

    @Paul Newcome just tagging you because you rock with formulas!


    Thanks!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Kelly Gabel If your dates are in a date type column then you are going to still want to use the 4 digit year in your DATE functions. If they are not in a date type column, we would need to do some things a little differently.

    thinkspi.com

  • Kelly GabelKelly Gabel ✭✭✭✭✭

    @Paul Newcome They are in a date type column, but using the 4 digit year still didn't work in that formula...

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Sorry. I completely missed this part...


    Switch your dates around. Your formula is currently saying to count dates that are less than 10/1 and greater than 12/31.


    You want greater than 10/1 and less than 12/31.

    thinkspi.com

  • Kelly GabelKelly Gabel ✭✭✭✭✭

    Thanks @Paul Newcome That fixed it!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help. 👍️

    thinkspi.com

  • @Paul Newcome

    Hi Paul,

    I would love your help with a formula if you don't mind. You helped me the other day with a similar formula and I was so thankful!

    I am trying to to do a count ifs formula to know the count between two dates and another criteria.

    For example, I have a Company column and I want to know how many times "Contractor" is listed but only when the date is between Jan 1 and March 31st. So dates greater than 2020/1/1 and 2020/3/31.

    Can you please help me? Thank you so much, and let me know if I need to provide greater detail.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Megan Harry You could use something like this...

    =COUNTIFS(Company:Company, "Contractor", Date:Date, AND(@cell >= DATE(2020, 01, 01), @cell <= DATE(2020, 03, 31)))

    thinkspi.com

  • edited 10/16/20

    @Paul Newcome Yay thank you so much Paul! I was so close with the formula I was trying, but you helped me get it! I am very appreciative! It worked perfectly!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Megan Harry Happy to help. 👍️

    thinkspi.com

  • @Paul Newcome

    Hi Paul,

    I'm attempting to use your formula referenced on 10/16/20 but am referring to 2 separate data ranges within that formula and am receiving an "Invaid Data Type" error:

    =COUNTIFS({RANGE 1}, [Category1]@row, AND({RANGE2}, AND(@cell >= DATE(2017, 8, 28), @cell <= DATE(2018, 8, 26))))

    I have found the following formula to be successful:

    =COUNTIFS({RANGE2}, AND(@cell >= DATE(2017, 8, 28), @cell <= DATE(2018, 8, 26))) but I wish to add references to those 2 separate data ranges.

    Can you please provide any insight into why I receive an error when adding references to data ranges?

    Thank you!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Michelle Bestercy You do not need the first AND statement.


    =COUNTIFS({RANGE 1}, [Category1]@row, {RANGE2}, AND(@cell >= DATE(2017, 8, 28), @cell <= DATE(2018, 8, 26)))

    thinkspi.com

  • @Paul Newcome

    Thank you so much, Paul! That solution worked for me.

Sign In or Register to comment.