IF, AND & OR Checking Multiple Value Combinations in Multiple Cells

Denny57

New Member
Using IF, AND & OR, I have managed to obtain TRUE & FALSE returns for 3 Cells where each has 2 possible values. There are 8 possible different Combinations and if any of these are TRUE the value of cell AF21 is returned but if FALSE then "0" is returned.

EXAMPLE
=IF(OR(AND(\$B\$5="EW",D19="W",E19="W",F19="P"),AND(\$B\$5="EW",D19="W",E19="W",F19="W"),AND(\$B\$5="EW",D19="W",E19="P",F19="W"),AND(\$B\$5="EW",D19="W",E19="P",F19="P"),AND(\$B\$5="EW",D19="P",E19="P",F19="P"),AND(\$B\$5="EW",D19="P",E19="P",F19="W"),AND(\$B\$5="EW",D19="P",E19="W",F19="P"),AND(\$B\$5="EW",D19="P",E19="W",F19="W")),AF21,0)
The cells are D19,E19 & F19 and Possible Values are "W" & "P"

I am looking to expand this for 5 Cells each with 2 possible values which will return 32 possible combinations.

Is there a valid syntax that will shorten the formula, perhaps by identifying each cell and each possible value. Each possible combination will return TRUE.

I welcome any possible resolutions

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

mikerickson

MrExcel MVP
I think that your formula could be replaced with
=IF(AND(\$B\$5="EW", (COUNTIF(D19:F19,"P")+COUNTIF(D19:F19,"W")=3)), AF21, 0)

Denny57

New Member
I think that your formula could be replaced with
=IF(AND(\$B\$5="EW", (COUNTIF(D19:F19,"P")+COUNTIF(D19:F19,"W")=3)), AF21, 0)
Thank you for this possible solution. Just for my understanding please can you let me know the relevance of =3

Many thanks

Denny57

New Member
Thank you for this possible solution. Just for my understanding please can you let me know the relevance of =3

Many thanks
Unfortunately, this formula does not work and I wonder if this is because there is no OR statement.

Aim : To get 3 criteria to return a TRUE / FALSE response when two of the criteria can have different but acceptable values.

New Simpler Example (This works fine with just 4 possible combinations). I need to expand this for up to 6 variable criteria (similar to Ctiteria 2 & 3 below) which would require 32 combinations.

=IF(OR(AND(\$B\$5="EW",C10="W",F10="P"),AND(\$B\$5="EW",C10="P",F10="W"),AND(\$B\$5="EW",C10="W",F10="W"),AND(\$B\$5="EW",C10="P",F10="P")),AF10,0)

Criteria 1: \$B\$5 = "EW" is a fixed value
Criteria 2: Cell C10 can have an acceptable variable value of W or P
Criteria 3: Cell F10 can have an acceptable variable value of W or P
So there are 4 possible combinations of values in cells C10 + F10 (W+W, W+P, P+W, P+P)

If any of these combinations of all three criteria are = TRUE then value of AF10 is populated in the cell.
eg C10 = W and F10 = P, or C10 = P and F10 = P
If Cells C10 or F10 contain any other value than W or P then FALSE then a value of 0 is populated in the cell.

I welcome any suggestions that will reduce the number of components in the expanded formula.

With thanks

mikerickson

MrExcel MVP

If D10:F10 is acceptable (i.e. either P or W) then COUNTIF(D10:F10,"P")+COUNTIF(D10:F10,"W")=3

so COUNTIF(D10:F10,"P")+COUNTIF(D10:F10,"W")=3

will return the same value as
OR(AND(D10="P",E10="P",F10="P"), AND(D10="P",E10="P",F10="E"), AND(D10="P",E10="E",F10="P"), AND(D10="P",E10="E",F10=""E")
, AND(D10="E",E10="P",F10="P"), AND(D10="E",E10="P",F10="E"), AND(D10="E",E10="E",F10="P"), AND(D10="E",E10="E",F10="E"))

Your description in post 4 you don't mention column E, but the formula in the OP references that column.

and overall (P and Q) or (P and R) is the same as P and (Q or R)

My AND(AND(\$B\$5="EW", (COUNTIF(D19:F19,"P")+COUNTIF(D19:F19,"W")=3)) is the equivalent of the OP condition.

For what set of values does the OP formula return a different result than mine?

Last edited:

Denny57

New Member
If D10:F10 is acceptable (i.e. either P or W) then COUNTIF(D10:F10,"P")+COUNTIF(D10:F10,"W")=3

so COUNTIF(D10:F10,"P")+COUNTIF(D10:F10,"W")=3

will return the same value as
OR(AND(D10="P",E10="P",F10="P"), AND(D10="P",E10="P",F10="E"), AND(D10="P",E10="E",F10="P"), AND(D10="P",E10="E",F10=""E")
, AND(D10="E",E10="P",F10="P"), AND(D10="E",E10="P",F10="E"), AND(D10="E",E10="E",F10="P"), AND(D10="E",E10="E",F10="E"))

Your description in post 4 you don't mention column E, but the formula in the OP references that column.

and overall (P and Q) or (P and R) is the same as P and (Q or R)

My AND(AND(\$B\$5="EW", (COUNTIF(D19:F19,"P")+COUNTIF(D19:F19,"W")=3)) is the equivalent of the OP condition.

For what set of values does the OP formula return a different result than mine?
Hi Mik

I provided a completely different example in my last post as I did not make it clear in my original enquiry that the cells to be looked up in the formula may not be consecutive (D10, E10,F10) and eventually may be any combination of any 2 from 5. I will need to apply each formula to many lines each of which will require the same formula structure but with different cells. For example one line might require all possible combinations of the values W & P for cells C11, D11 & G11 and the next line might use cells D12, E12 & G12.

The newer simpler example and explanation better indicate what I am hoping to achieve and how I need to be able to expand.

Please ignore my OP and I would appreciate help with the new example.

Also, I could not understand the =3 in your original response. I am learning more about Excel all the time so an explanation would really help me to understand the formula.

Many thanks

mikerickson

MrExcel MVP

If your main data is scattered in many discontinuous columns, with many options, you are going to end up with unwieldy formulas.
One workaround would be to put formulas like =D2 in CA2, =G2 in CB2, =H2 in CC2 in helper columns way off to the right.
Then your testing could refer to those helper cells without having to accommodate discontinuity.

Or you might go the VBA/UDF route. But native formulas really aren't built for discontinuous regions.

Eric W

MrExcel MVP
Mike is right in that discontinuous ranges are a pain to work with, but this should handle your example from post 4:

=IF(AND(\$B\$5="EW",OR(C10={"P","W"}),OR(F10={"P","W"})),AF10,0)

Expanding it to 6 criteria may or may not be a problem, depending on exactly how the criteria work out. Using MATCH or SEARCH could be an option too.

Denny57

New Member
Mike is right in that discontinuous ranges are a pain to work with, but this should handle your example from post 4:

=IF(AND(\$B\$5="EW",OR(C10={"P","W"}),OR(F10={"P","W"})),AF10,0)

Expanding it to 6 criteria may or may not be a problem, depending on exactly how the criteria work out. Using MATCH or SEARCH could be an option too.
Eric
Thanks you for your response and this is the sort of structure I was hoping might be available. This will also allow for minimal changes to be made when Row number change. I will test this later today.

Denny57

New Member
Eric

Thanks you, this works perfectly.

Mik

Thanks for your help with this

Replies
5
Views
32
Replies
8
Views
111
Replies
0
Views
177
Replies
3
Views
175
Replies
1
Views
91

1,129,265
Messages
5,635,158
Members
416,844
Latest member
ryanangus496

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back