Home >Topics >excel >How to use multiple conditions of excelif function

How to use multiple conditions of excelif function

尚
Original
2019-06-14 16:14:46197182browse

How to use multiple conditions of excelif function

Excel if function syntax:

1. Expression: IF (logical_test,[value_if_true],[value_if_false])

Chinese expression: if (condition, the operation to be performed when the condition is true, the operation to be performed when the condition is false)

2. Description: [value_if_true] and [value_if_false] represent optional items, that is, they can Do not write

if. The steps to use multiple conditions are as follows:

Example: If you want to mark the clothing sales table, the "major category" is "women's clothing" and the "price" is greater than For clothing that is equal to 80 and the "sales volume" is greater than 800, the operation process steps are as follows

1. Use multiple ifs nested:

How to use multiple conditions of excelif function

Operation process step description: Select cell H2 and put the formula =IF(C2="Women's Clothing",IF(E2>=80,IF(F2>800,"Satisfies the condition","Does not satisfy the condition"),"Does not meet the condition"),"No "Conditions met") is copied to H2 and press Enter to return to "Conditions not met"; select H2 again, move the mouse to the cell fill handle of H2, hold down the left button and drag down, the cells passed by Fill it with "conditions not met" and press Ctrl S to save. Similarly, H3 is filled with "conditions met", and other cells are still filled with "conditions not met".

Formula description: =IF(C2="Women's Clothing",IF(E2>=80,IF(F2>800,"Satisfies the conditions","Does not meet the conditions"),"Does not meet the conditions"), "The condition is not met")

consists of three ifs, that is, two ifs are nested in one if. The condition of the first if is C2="women's clothing". If the condition is true, then execute IF(E2>=80,IF(F2>800,"Condition satisfied","Condition not satisfied"),"Condition not satisfied" ); otherwise it returns "condition not met". The condition of the second if is E2>=80. If the condition is true, then execute IF(F2>800, "Condition is met", "Condition is not met"), otherwise "Condition is not met" is returned. The condition of the third if is F2>800. If the condition is true, it returns "the condition is met", otherwise it returns "the condition is not met".

2. Use And to combine multiple conditions to form an "AND" relationship

Put the multi-if nested formula in the above example =IF(C2="Women's Clothing",IF(E2> =80,IF(F2>800,"Conditions are met","Conditions are not met"),"Conditions are not met"),"Conditions are not met") Use And combination instead. The operation steps are as follows

How to use multiple conditions of excelif function

Instructions for steps in the operation process: Select cell H2 and put the formula =IF(AND(C2="Women's Clothing",E2>=80,F2>800),"Satisfies the condition", "Does not satisfy the condition" Condition") is copied to H2 and press Enter to return "Condition not met"; similarly drag down and save, returning the same result as the previous example, indicating that the formula is correct.

Formula description:

=IF(AND(C2="Women's Clothing",E2>=80,F2>800),"Satisfies the conditions","Does not meet the conditions")

The formula uses the And function to combine three conditions, namely C2="Women's clothing",E2>=80,F2>800. When three conditions are met at the same time (i.e. AND(C2="Women's clothing",E2>= 80,F2>800) returns "true"), returns "condition is met", otherwise returns "condition is not met".

3. Use Or to combine multiple conditions to form an "or" relationship

Combine And in the above example with multiple conditions formula =IF(AND(C2="Women's Clothing",E2> ;=80,F2>800), "conditions are met", "conditions are not met") Use Or combination instead, the operation process steps are as follows:

How to use multiple conditions of excelif function

Operation process step description : Select the H2 cell, copy the formula =IF(OR(C2="Women's Clothing",E2>=80,F2>800),"Meet the conditions","Do not meet the conditions") to H2, press Enter to return "Satisfy the conditions"; drag down and save, and all return to "Satisfy the conditions".

Formula description: =IF(OR(C2="Women's Clothing",E2>=80,F2>800),"Satisfies the conditions","Does not meet the conditions")

Use Or for the formula The function combines three conditions, namely C2="Women's clothing",E2>=80,F2>800, that is, OR(C2="Women's clothing",E2>=80,F2>800), which means: as long as one condition is met , it will return "true"; it will return "false" if none of the conditions are met. In the demonstration, each record satisfies a condition, so all records "satisfy the condition" are returned.

For more Excel-related technical articles, please visit the Excel Basic Tutorial column!

The above is the detailed content of How to use multiple conditions of excelif function. For more information, please follow other related articles on the PHP Chinese website!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn