COUNTIFS + OR

08/03/20 Edited 08/03/20
Accepted

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 PGenevieve P admin
    Accepted 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

Answers

Sign In or Register to comment.