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
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!