=IF (multiple criterion) then mark as "Yes"

Daniel Barber

I am trying to make a cell read "Yes' or "No" based on criterion from another sheet. Basically, if all projects under a certain package have set schedules then the cell should read "Yes" and vice versa "No".

The issue is that there are some parameters needed to determine if a project is under a certain package. A Project will not have the Release checkbox marked, it will have the Package name referenced in the other sheet, and it will have a "Yes" from the Schedule dropdown column. Something similar to this:

Ive been using this formula but it is returning a "No" even though all projects are checked "Yes". Im wondering if its including the parent row as well?

=IF(AND(COUNTIFS({Project Intake Sheet Range 5}, [Primary Column]22, {Project Intake Sheet Range 8}, =0, {Project Intake Sheet Range 4}, =1)), "Yes", "No")

Where Range 5 is the Package column, [Primary Column]22 is the specific Package Im referencing (Package 1), Range 8 is the Release column marked as unchecked, and Range 4 is the Schedule column referencing "Yes"

Can someone point me in the right direction please? Thank you!

Georgie

Hi @Daniel Barber,

You can do this using an IF(COUNTIFS) formula that compares the number of rows that have the same package name/number, the Schedule Set as “Yes” and Release unchecked to the number of rows that have the same package number and Release unchecked. 

That is, if my source sheet looks like this,:

then my destination sheet, where I’ll be placing the formula, will look something like this:

You can see the formula I used here:

I’ll type the formula here as well:

• =IF(COUNTIFS({Package column}, [Primary Column]@row, {Release column}, 0, {Schedule Set column}, "Yes") = COUNTIFS({Package column}, [Primary Column]@row, {Release column}, 0), "Yes", "No")

Each cross-sheet reference in the formula is the named column in the source sheet.

This is saying: If the number of rows where the value in the Package column on the source sheet has the same value as the Primary Column cell on this row, have Release unchecked and Schedule Set is “Yes” is equal to the number of rows where the value in the Package column on the source sheet has the same value as the Primary Column cell on this row and have Release unchecked, return “Yes”. If not, return “No”.

To simplify, the formula compares the number of child rows in the source sheet with the same Package name (since all child rows in your screenshot are unchecked in the Release column) to the number of child rows with the same Package name that have Schedule Set as “Yes” - if those numbers match, meaning that all child rows display “Yes”, then the formula returns “Yes”, but if at least one of the child rows has “Schedule Set” as “No”, the formula will display “No”. 

Check out the following function articles for more information:

• IF Function
• COUNTIFS Function

Hope that helps - let me know if you have any questions!

Georgie

Georgie

Hi @Daniel Barber,

You can do this using an IF(COUNTIFS) formula that compares the number of rows that have the same package name/number, the Schedule Set as “Yes” and Release unchecked to the number of rows that have the same package number and Release unchecked. 

That is, if my source sheet looks like this,:

then my destination sheet, where I’ll be placing the formula, will look something like this:

You can see the formula I used here:

I’ll type the formula here as well:

• =IF(COUNTIFS({Package column}, [Primary Column]@row, {Release column}, 0, {Schedule Set column}, "Yes") = COUNTIFS({Package column}, [Primary Column]@row, {Release column}, 0), "Yes", "No")

Each cross-sheet reference in the formula is the named column in the source sheet.

This is saying: If the number of rows where the value in the Package column on the source sheet has the same value as the Primary Column cell on this row, have Release unchecked and Schedule Set is “Yes” is equal to the number of rows where the value in the Package column on the source sheet has the same value as the Primary Column cell on this row and have Release unchecked, return “Yes”. If not, return “No”.

To simplify, the formula compares the number of child rows in the source sheet with the same Package name (since all child rows in your screenshot are unchecked in the Release column) to the number of child rows with the same Package name that have Schedule Set as “Yes” - if those numbers match, meaning that all child rows display “Yes”, then the formula returns “Yes”, but if at least one of the child rows has “Schedule Set” as “No”, the formula will display “No”. 

Check out the following function articles for more information:

• IF Function
• COUNTIFS Function

Hope that helps - let me know if you have any questions!

Georgie

Daniel Barber

Worked like a charm, thank you!