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?

Tags:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!