Multiple Countifs with Or statements

Matthew Emrich

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

Matthew Emrich

@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)))

Paul Newcome

Initially I see that the OR statement isn't being used correctly.

You have (for example)

=IF("option a", OR("option b", OR("option c", .................

The correct syntax is

=IF(OR("option a", "option b", "option c"), value if true)

You also want to make sure that you are closing off each COUNTIFS before moving on to the next one and you will need to have some kind of argument for each COUNTIFS such as "greater than 1" kind of thing.

=IF(OR(COUNTIFS({Tech 1}, .....) > 1, COUNTIFS({Tech 2}, .....) > 1, COUNTIFS({Tech 3}, .....) > 1), value if true)

Matthew Emrich

@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)))

Paul Newcome

Glad you were able to find a working solution. 👍️