CountIf Date Range Overlaps With Another Date Range

[EvonS]

From month to month I'd like to know how many people are still on their work placements. Columns J and K represent the start and end dates of the placement, where columns N represents the last day of the month and column M represents the first day of the month. Column I represent student outcomes (but I'm only concerned with the "Placement" outcome"). This formula =IF(OR(J2>N4,K2<M4),0, COUNTIFS(I2:I22,"Placement")), work perfectly when I test it as is. However when I add ranges (J2:J1000 and K2:K1000) it just keeps giving me a 0. Anyone have any ideas?

 

[MarcelBeug]

This formula

=IF(OR(J2>N4,K2<M4),0, COUNTIFS(I2:I22,"Placement"))

, work perfectly when I test it as is. However when I add ranges (J2:J1000 and K2:K1000) it just keeps giving me a 0. Anyone have any ideas?

Welcome to the forum.

You can use PHP tags to prevent issues with > or < being interpreted as HTML-code.

With regard to your formula it looks to me like you should use fixed references, e.g. $I$2:$I$22.
Probably the first and last day of the month should also be fixed to $N$4 and $M$4.

Otherwise, the references shift when copying the formula.

 

[EvonS]

Thanks for your help and your quick reply. Unfortunately the fixed references didn't work

=IF(OR($J$2:$J$1000>$N$4,$K$2:$K$1000<$M$4),0, COUNTIFS($I$2:$I$22,"Placement"))