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
 Smartsheet Customer Resources
 62.2K Get Help
 360 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!