COUNTIFS + OR

Damon Lloyd

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

Genevieve P.

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

Genevieve P.

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

Damon Lloyd

That works, thanks Genevieve!

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

Genevieve P.

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