IF function with AND function – multiple conditions
The AND function allows you to have multiple conditions in an IF function, you can have up to 254 arguments. An argument is an input value given to a function. You construct a logical expression that you use as an argument in the AND function.
Table of Contents
1. IF with AND function - two logical expressions
Formula in cell D3:
1.1 Explaining formula in cell D3
The IF function above checks two conditions, the "Region" value must match a text string and the "Amount" value must be larger than a number. If both conditions return TRUE the AND function returns TRUE.
IF REGION = value AND Amount > number then TRUE Else FALSE
In other words, all logical tests in each argument in the AND function must return TRUE for the AND function to return TRUE. The AND function returns FALSE if at least one argument returns FALSE.
Step 1 - Check if value equals condition
The equal sign compares value to value, it returns a boolean value TRUE or FALSE.
B3="South America"
becomes
"North America"="South America"
and returns FALSE.
Step 2 - Check if the number is greater than the condition
The larger than character lets you check if a value is larger than another value, it also returns a boolean value TRUE or FALSE.
C3>5
becomes
5>5
and returns FALSE. Five is not larger than five.
Step 3 - Evaluate AND function
The AND function returns a boolean value TRUE or FALSE if all arguments evaluate to TRUE or the numerical equivalent which is one.
AND(B3="South America", C3>5)
becomes
AND(FALSE, FALSE)
and returns FALSE.
Step 4 - Evaluate IF function
The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.
IF(logical_test, [value_if_true], [value_if_false])
IF(AND(B3="South America", C3>5), TRUE, FALSE)
becomes
IF(FALSE, TRUE, FALSE)
and returns FALSE.
1.2 Alternative formula
You can shorten the formula somewhat by enclosing each logical expression with parentheses and then multiply the conditions.
Formula in cell D4:
Step 1 - Check the first condition
The first logical expression in cell B4 is (B4="South America") and it returns TRUE.
B4="South America"
becomes
"South America"="South America"
and returns TRUE.
Step 2 - Check the second condition
The second expression (C4>5) also returns TRUE.
C4>5
becomes
7>5
and returns TRUE.
Step 3 - Multiply boolean values
(B4="South America")*(C4>5)
becomes
TRUE * TRUE
equals 1. TRUE multiplied by TRUE is 1. TRUE is the same thing as 1 and FALSE is 0 (zero). If you multiply boolean values the outcome is always 0 (zero) or 1.
Step 4 - Evaluate IF function
The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.
IF(logical_test, [value_if_true], [value_if_false])
IF((B4="South America")*(C4>5), TRUE, FALSE)
becomes
IF(TRUE, TRUE, FALSE)
and returns TRUE.
2. IF with AND function - multiple pairs of criteria
The formula in cell D3 checks if any of the criteria pairs in cell range F4:G6 matches cell B3 and C3 respectively.
Formula in cell D3:
2.1 Explaining formula in cell D3
Step 1 - Check multiple conditions
The COUNTIFS function calculates the number of cells across multiple ranges that equals all given conditions.
It allows you to use up to 254 arguments or 127 criteria pairs.
Excel Function Syntax
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
COUNTIFS($F$4:$F$6,B3,$G$4:$G$6,C3)
becomes
COUNTIFS({"South America"; "Asia"; "Middle east"}, "North America", {5; 7; 6}, 7)
and returns 0 (zero).
Step 2 - Evaluate IF function
The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.
IF(logical_test, [value_if_true], [value_if_false])
IF(COUNTIFS($F$4:$F$6,B3,$G$4:$G$6,C3), TRUE, FALSE)
becomes
IF(0, TRUE, FALSE)
and returns FALSE in cell D3. 0 (zero) is the numerical equivalent to FALSE.
Cell D4 returns TRUE, both cells B4 and C4 match cells F4 and G4.
3. Using arrays in the AND function
The array formula above lets you check if all values in cell range B3;B5Â are not empty and if all numbers in C3:C5 are above 0 (zero). It returns TRUE if all conditions are TRUE.
Formula in cell B8:
There are many conditions in the above formula and the array formula lets you do this without problems.
To enter an array formula press and hold CTRL + SHIFT simultaneously, then press Enter once. Release all keys.
The formula bar now shows the formula enclosed with curly brackets telling you that you entered the formula successfully. Don't enter the curly brackets yourself.
3.1 Explaining formula in cell B8
Step 1 - Check if all values in the array are non-empty
The less than and larger than characters combined lets you check if a value is not equal to another value. The result is a boolean value TRUE or FALSE.
This can be performed to an array of values as well, it returns as many boolean values as there are values in the array.
B3:B5<>""
becomes
{"South America";"Asia";"Middle east"}<>""
and returns {TRUE; TRUE; TRUE}. All values in the cell range are not equal to nothing "".
Step 2 - Check if values are larger than zero
The larger than character lets you check if a value is larger than another value, it also returns a boolean value TRUE or FALSE.
C3:C5>0
becomes
{5; 7; 6}>0
and returns {TRUE; TRUE; TRUE}.
Step 3 - Check if all values are TRUE
The AND function returns a boolean value TRUE or FALSE if all arguments evaluate to TRUE or the numerical equivalent which is one.
AND(B3:B5<>"",C3:C5>0)
becomes
AND({TRUE; TRUE; TRUE}, {TRUE; TRUE; TRUE})
and returns TRUE.
Step 4 - Evaluate IF function
The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.
IF(logical_test, [value_if_true], [value_if_false])
IF(AND(B3:B5<>"",C3:C5>0), TRUE, FALSE)
becomes
IF(TRUE, TRUE, FALSE)
and returns TRUE.
4. Get Excel *.xlsx file
Logic category
This article demonstrates several ways to check if a cell contains any value based on a list. The first example […]
This article demonstrates formulas that perform a partial match for a given cell using multiple strings specified in cells F2 […]
The image above demonstrates a formula that matches a value to multiple conditions, if the condition is met the formula […]
Excel categories
3 Responses to “IF function with AND function – multiple conditions”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
If"different places" then will different number with multiple condition. Do you know the formula?
if KL = 23565
IF Sel = 24633
IF Johor = 16842
IF Melacca = 19014
FORMULA NOT WORKING
OR(MATCH("*FT*",A2:F2,0) = TRUE,MATCH("*MTR*",A2:F2,0))= FALSE, RESULT = " "
BUT SINGLE FORMULA WORKING LIKE- ("*FT*",A2:F2,0) = TRUE
("*MTR*",A3:F3,0)= TRUE, BUT NOT WORKING WITH OR
IF(VALUE IF FALSE) ALSO NOT WORKING
IF(A2="FT",A2,IF(A2="MTR",A2))
HERE A2=FT
SAME FORMULA IN A3=MTR, NOT WORKING
USING OFFICE 2016
IF(F2=Single,IF(C2>600,"as per Slplit logic",IF(C2>450,"as per split logic",IF(C2>300,"as per split logic","1 Sup & 1 Super FE"))),AND(IF(F2=Multi,IF(C2>1200,"As per split logic",IF(C2>900,"1 DCH",IF(C2>600,"1 DCH",IF(C2<600,"1DCH")))))))