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

Denny57

New Member
Joined
Nov 23, 2015
Messages
49
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 15, 2007
Messages
23,980
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
Joined
Nov 23, 2015
Messages
49
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 23, 2015
Messages
49
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 15, 2007
Messages
23,980

ADVERTISEMENT

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
Joined
Nov 23, 2015
Messages
49
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 15, 2007
Messages
23,980

ADVERTISEMENT

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
Joined
Aug 18, 2015
Messages
10,797
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.
 
Solution

Denny57

New Member
Joined
Nov 23, 2015
Messages
49
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 23, 2015
Messages
49
Office Version
  1. 365
Platform
  1. Windows
Eric

Thanks you, this works perfectly.

Mik

Thanks for your help with this
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top