Multiple Countifs with Or statements

I need to create formula, where it will put a 1 if a person is assigned to an activity during a given time frame.

What I'm looking to do is search for a name in Sheet B and see if it falls in a given time frame. The issue I'm having is that the name can be in any of the columns Tech1 to 6 in my second sheet. I am trying to use multiple countifs with or statements. This is the formula I'm trying

=IF($[Tech Start]@row > [8.00]$2, "", IF($[Tech End]@row <= [8.00]$2, "", COUNTIFS({Tech 1}, $Tech@row, {Start 1}, <=[8.00]$1, {End 1}, >[8.00]$1, OR(COUNTIFS({Tech 2}, $Tech@row, {Start 2}, <=[8.00]$1, {End 2}, >[8.00]$1, OR(COUNTIFS({Tech 3}, $Tech@row, {Start 3}, <=[8.00]$1, {End 3}, >[8.00]$1), OR(COUNTIFS({Tech 4}, [6.00]@row, {Start 4} <= [8.00]$1, {End 4}, >[8.00]$1), OR(COUNTIFS({Tech 5}, $Tech@row, {Start 5}, <=[8.00]$1, {Copy of Week A-Monday-Needs test Range 2}, >[8.00]$1), OR(COUNTIFS({Tech 6}, $Tech@row, {Start 6}, <=[8.00]$1, {End 6}

The first part of the formula that is barred out is excluding when they are not working


Sheet A

Sheet B


Best Answer

  • Matthew Emrich
    Matthew Emrich ✭✭✭✭✭
    Answer ✓

    @Paul Newcome Thanks for the help. I was not able to get the formula with or( to work, but due to the nature of the data (the person can only exist in 1 column), I was able to get by with the following formula. I added the countifs together.

    =IF($[Tech Start]@row > [6.00]$2, "", IF($[Tech End]@row <= [6.00]$2, "", COUNTIFS({Tech 1}, $Tech@row, {Start 1}, <=[6.00]$1, {End 1}, >[6.00]$1) + COUNTIFS({Tech 2}, $Tech@row, {Start 2}, <=[6.00]$1, {End 2}, >[6.00]$1) + COUNTIFS({Tech 3}, $Tech@row, {Start 3}, <=[6.00]$1, {End 3}, >[6.00]$1) + COUNTIFS({Tech 4}, $Tech@row, {Start 4}, <=[6.00]$1, {End 4}, >[6.00]$1) + COUNTIFS({Tech 5}, $Tech@row, {Start 5}, <=[6.00]$1, {End 5}, >[6.00]$1) + COUNTIFS({Tech 6}, $Tech@row, {Start 6}, <=[6.00]$1, {End 6}, >[6.00]$1)))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!