# COUNTIF between Dates

Options
edited 12/09/19

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

• Options

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

so breaking these down

=COUNTIFS(

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

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

• ✭✭✭✭✭✭
Options

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.

• Options

Thank you very much!  This did work.

• ✭✭✭✭✭✭
Options

You're welcome! Glad I could help.

• ✭✭
Options

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?

• ✭✭✭✭✭✭
Options

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!