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

Find more posts tagged with

Formulas

Legal

Project Management

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…