Count If with Start and End Date

pturnbull

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:

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

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

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.