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 AMondayNeeds 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

@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

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)

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

Glad you were able to find a working solution. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!