COUNTIFS with multiple OR dates
Im trying to use a formula that counts the number of projects where it fulfills certain criteria that then checks whether the startdate was after a beginning date range or the end date was before the end date range. (basically that work was performed within the date range).
This is what I have:
=COUNTIFS({Marcom Portfolio Campus Department}, HAS(@cell, $[Column12]$1), {Marcom Portfolio Type}, CONTAINS([Column #1]37, @cell), {Marcom Portfolio Start Date}, >=DATE(2021, 1, 1) {Marcom Portfolio End Date}, <=DATE(2021, 12, 31))
This doesn't work because its possible that work started before the date range but ended before the date range and that wouldn't be counted. I basically need an or statement where Start Date if after beginning range OR End Date is before end range.
Thank you for any help,
John
Best Answer
-
In that case, you should be able to just adjust your logic. What you want is any End Date greater than or equal to Jan 1 and any Start Date that is less than or equal to Dec 31.
This will cover any set of dates where the start is before or during the year in question and the end is during or after the year in question.
Four things it will pick up:
Start Before & End During
Start Before & End After
Start During & End During
Start During & End After
Answers
-
So basically any overlap? If the end date is in 2021 or the start date is in 2021 (or both are in 2021)?
-
Yes, any overlap.
-
In that case, you should be able to just adjust your logic. What you want is any End Date greater than or equal to Jan 1 and any Start Date that is less than or equal to Dec 31.
This will cover any set of dates where the start is before or during the year in question and the end is during or after the year in question.
Four things it will pick up:
Start Before & End During
Start Before & End After
Start During & End During
Start During & End After
-
Thank you Paul!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!