COUNTIF between Dates


I have a formula (see below) where I'm trying to get a count where multiple criteria apply.  The first date range renders a correct count but fails once the second one is added.  I've tried multiple iterations of the formula, this is my current one.  If it is not clear from the formula, I'm trying to count tasks assigned to someone that is 'not started' and falls within 15 day of TODAY.

=COUNTIFS({DSKVIR Assigned To}, Resource2, {Status}, "Not Started", AND({DSKVIR - ML Range 4}, >TODAY(-15), {DSKVIR - ML Range 4}, <TODAY(15)))


  • rjudenberg

    the format for  Countifs is range1, criteria1, range2, criteria2...etc

    so breaking these down


    {DSKVIR Assigned To}, Resource2,  -- range/criteria 1  (which resource)

    {Status}, "Not Started", -- range/criteria 1  (what status)


    here is where you went wrong

    AND({DSKVIR - ML Range 4}, >TODAY(-15), {DSKVIR - ML Range 4}, <TODAY(15)))  --


    try this instead 

    @{DSKVIR - ML Range 4},AND(@cell >TODAY(-15), @cell, <TODAY(15)))

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    For countifs, You don't can't have AND statements intermingled. If you break up your and statement and just make each of those a requirement it should work just fine. 

    =COUNTIFS({DSKVIR Assigned To}, Resource2, {Status}, "Not Started", {DSKVIR - ML Range 4}, >TODAY(-15), {DSKVIR - ML Range 4}, <TODAY(15))

    Try that and see if it works. It will only count the item if all of those criteria apply.

  • Thank you very much!  This did work.

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    You're welcome! Glad I could help. 

  • AMJames
    AMJames ✭✭

    I'm trying to count a number of bills. The criterion I need to use is from the data from one of the columns named PROPOSED DATE. I want to only count bills whose proposed date is within the last 30 days. I can't seem to make a formula that captures this info. Where I am going wrong? 

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    AMJames, can you share a screenshot of your sheet? 

    =COUNTIFS([Proposed Date]:[Proposed Date], >=TODAY(), [Proposed Date]:[Proposed Date], <=TODAY(30))


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!