COUNTIF between Dates

bsemingson30431

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

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

 

try this instead 

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

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.

bsemingson30431

Thank you very much!  This did work.

Mike Wilday

You're welcome! Glad I could help. 

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

AMJames, can you share a screenshot of your sheet? 

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