COUNTIFS with multiple OR dates

Options
J. Drevs
J. Drevs
edited 05/01/24 in Formulas and Functions

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

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!