Count If with Start and End Date

I need to count the number of projects going on in each quarter. I currently am using this formula: =COUNTIFS({Start Date}, <=DATE(2023, 7, 1), {End Date}, >=DATE(2023, 9, 30), {Helper}, 1)

However, I need to include my Start and End dates to get an accurate count.

This is my roll-up sheet:


Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I am assuming you are getting an inaccurate count?


    Exactly what qualifies as being in a quarter? Is it where start and end dates are both within the quarter, or if there is even one day within that quarter it counts?

  • Ipshita
    Ipshita ✭✭✭✭✭✭

    Hello @pturnbull

    Ideally the following COUNTIF formula should work in your case, I don't know what you have in the helper column.

    =COUNTIF(([Start Date]@row <= DATE(2022, 7, 15)), [End Date]@row >= DATE(2022, 9, 30))

    Also, you can have your quarter in a separate column if that helps =IF(ISDATE([End Date]@row), IF(MONTH([End Date]@row) <= 3, "Q1", IF(MONTH([End Date]@row) <= 6, "Q2", IF(MONTH([End Date]@row) <= 9, "Q3", "Q4"))))

    Cheers!

    Ipshita

    Ipshita Mukherjee

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Ipshita Your COUNTIF should actually be a COUNTIFS, and your formula will only grab rows where the Start Date is in Q2 or earlier and the End Date is in Q4 or later. If the start and/or end date is in Q3, then your formula will not grab it.


    @pturnbull If you are looking to count anything that overlaps even by a day, you would need to count rows where the start date is less than the quarter end date and the end date is greater than the quarter start date.

    =COUNTIFS({Start Date}, @cell<= [Quarter End Date]@row, {End Date}, @cell>= [Quarter Start Date]@row)


    If you are just looking for something like a count of active tasks in each quarter, then this should get the job done, but if you wanted something to count for only a single quarter, then we would have to use different logic.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!