Combine Countifs and OR formulas

Warren123

I need to get a count of coworkers taking PTO on a specific week. My current sheet counts the week number PTO starts and ends (separate column for each). Is it possible to combine a COUNTIF and OR formulas?

A specific example of where I'm stuck is if someone took a long weekend from 4/1/22 to 4/4/22. My sheet will show start date as week 13 and end date as week 14. If I use =Countifs(pto start week, week 13, pto end week, week 13), it will not count the long weekend PTO since week numbers don't match. If I use =Countif(pto start week, week 13) + Countif(pto end week, week 13), it will count someone who took a full week off twice.

Is there a way to countif the start week OR end week = week 13?

Paul Newcome

Try something like this...

=COUNTIFS({PTO Start Week}, @cell <= 13, {PTO End Week}, @cell >= 13)

This will also count if someone takes an extended vacation that spans (for example) 15 weeks. Using the "equals" argument would miss these people if their PTO started on week 12 and ended on week 14.

By saying the start is less than or equal to and the end is greater than or equal to should grab everyone.

Paul Newcome

Try something like this...

=COUNTIFS({PTO Start Week}, @cell <= 13, {PTO End Week}, @cell >= 13)

This will also count if someone takes an extended vacation that spans (for example) 15 weeks. Using the "equals" argument would miss these people if their PTO started on week 12 and ended on week 14.

By saying the start is less than or equal to and the end is greater than or equal to should grab everyone.