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)))
Comments
-
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)))
-
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.
-
You're welcome! Glad I could help.
-
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?
-
AMJames, can you share a screenshot of your sheet?
=COUNTIFS([Proposed Date]:[Proposed Date], >=TODAY(), [Proposed Date]:[Proposed Date], <=TODAY(30))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!