COUNTIFS with multiple OR dates

J. Drevs

Im trying to use a formula that counts the number of projects where it fulfills certain criteria that then checks whether the startdate was after a beginning date range or the end date was before the end date range. (basically that work was performed within the date range).

This is what I have:

=COUNTIFS({Marcom Portfolio Campus Department}, HAS(@cell, $[Column12]$1), {Marcom Portfolio Type}, CONTAINS([Column #1]37, @cell), {Marcom Portfolio Start Date}, >=DATE(2021, 1, 1) {Marcom Portfolio End Date}, <=DATE(2021, 12, 31))

This doesn't work because its possible that work started before the date range but ended before the date range and that wouldn't be counted. I basically need an or statement where Start Date if after beginning range OR End Date is before end range.

Thank you for any help,

John

Paul Newcome

In that case, you should be able to just adjust your logic. What you want is any End Date greater than or equal to Jan 1 and any Start Date that is less than or equal to Dec 31.

This will cover any set of dates where the start is before or during the year in question and the end is during or after the year in question.

Four things it will pick up:

Start Before & End During

Start Before & End After

Start During & End During

Start During & End After

Paul Newcome

So basically any overlap? If the end date is in 2021 or the start date is in 2021 (or both are in 2021)?

J. Drevs

Yes, any overlap.

Paul Newcome

In that case, you should be able to just adjust your logic. What you want is any End Date greater than or equal to Jan 1 and any Start Date that is less than or equal to Dec 31.

This will cover any set of dates where the start is before or during the year in question and the end is during or after the year in question.

Four things it will pick up:

Start Before & End During

Start Before & End After

Start During & End During

Start During & End After

J. Drevs

Thank you Paul!

Paul Newcome

Happy to help. 👍️