Combine Countifs and OR formulas
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?
Best Answer

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.
Answers

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.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 68 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!