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

· Share on FacebookShare on Twitter

Best Answer

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

    · Share on FacebookShare on Twitter

Answers

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

    · Share on FacebookShare on Twitter
  • Damon Lloyd
    Damon Lloyd ✭✭

    That works, thanks Genevieve!

    It's as if i had forgotten 1st grade arithmetic; a plus sign!

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin

    Haha, no worries at all! Glad that worked for you 😊

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!