COUNTIFS + OR

Damon Lloyd
Damon Lloyd ✭✭
edited 08/03/20 in Formulas and Functions

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

  • Genevieve P.
    Genevieve P. Employee
    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

  • Genevieve P.
    Genevieve P. Employee
    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

  • 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!