# COUNTIFS with multiple OR dates

✭✭
edited 05/01/24

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

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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

• ✭✭

Yes, any overlap.

• ✭✭✭✭✭✭

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

• ✭✭

Thank you Paul!

• ✭✭✭✭✭✭

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!