COUNTIFS + OR
Each quarter completed jobs get moved to a Completed Sheet for that relevant quarter. However, some of the tasks done on that job needs to be reported for a previous quarter.
An example of a task would be a Preflight Review, which takes place at the onset of a job, meaning the job is very likely to complete in a following month.
Currently my monthly reporting of the Preflight Reviews done on completed jobs looks like this:
=COUNTIFS({COMPLETED 2020 Q2 PREFLIGHT DONE}, >DATE(2020, 5, 31), {COMPLETED 2020 Q2 PREFLIGHT DONE}, <DATE(2020, 7, 1))
This formula works, except does not capture those jobs preflighted in June, but completed in July, since the quarter has changed.
I've tried variations of the below formula to incorporate the orphaned tasks, to no success.
=COUNTIFS({COMPLETED 2020 Q2 PREFLIGHT DONE}, >DATE(2020, 5, 31), {COMPLETED 2020 Q2 PREFLIGHT DONE}, <DATE(2020, 7, 1)), OR({COMPLETED 2020 Q3 PREFLIGHT DONE}, >DATE(2020, 5, 31), {COMPLETED 2020 Q3 PREFLIGHT DONE}, <DATE(2020, 7, 1))
Best Answer
-
Hi @Damon Lloyd
What about adding the two COUNTIFS together, instead?
Try this:
=COUNTIFS({COMPLETED 2020 Q2 PREFLIGHT DONE}, >DATE(2020, 5, 31), {COMPLETED 2020 Q2 PREFLIGHT DONE}, <DATE(2020, 7, 1)) + COUNTIFS({COMPLETED 2020 Q3 PREFLIGHT DONE}, >DATE(2020, 5, 31), {COMPLETED 2020 Q3 PREFLIGHT DONE}, <DATE(2020, 7, 1))
I'm not sure if this will create any duplicate COUNTS though... this may depend on how your sheet is set up and what those references are looking at.
Let me know if this works for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
Hi @Damon Lloyd
What about adding the two COUNTIFS together, instead?
Try this:
=COUNTIFS({COMPLETED 2020 Q2 PREFLIGHT DONE}, >DATE(2020, 5, 31), {COMPLETED 2020 Q2 PREFLIGHT DONE}, <DATE(2020, 7, 1)) + COUNTIFS({COMPLETED 2020 Q3 PREFLIGHT DONE}, >DATE(2020, 5, 31), {COMPLETED 2020 Q3 PREFLIGHT DONE}, <DATE(2020, 7, 1))
I'm not sure if this will create any duplicate COUNTS though... this may depend on how your sheet is set up and what those references are looking at.
Let me know if this works for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
That works, thanks Genevieve!
It's as if i had forgotten 1st grade arithmetic; a plus sign!
-
Haha, no worries at all! Glad that worked for you 😊
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 208 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 297 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!