Excel Formula for Three Conditions Returning 1 or 0
Question details
Create a formula that evaluates three variables and returns 1 or 0.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Evaluate a buy or sell action, a red or green category, and a positive or negative number.
- Observed behavior
- The desired result is 1 or 0 based on the specified combination of conditions.
Identify the cells containing the action, color, and number, and decide which combinations should return 1. The examples below use A9, B9, and C9.
Use IF and AND for one matching combination
Use this formula when one specific combination should return 1 and all other combinations should return 0.
Select an empty result cell and enter =IF(AND(A9="sell",B9="red",C9>0),1,0).
Press Enter. The formula returns 1 only when A9 is sell, B9 is red, and C9 is greater than zero; otherwise, it returns 0.
Drag the result cell's fill handle down to evaluate the corresponding cells in other rows.

Use a lookup table for different combinations
Use a lookup table when several combinations need their own 1 or 0 result.
Evaluate Three Conditions in WPS Spreadsheet
Use IF and AND in WPS Spreadsheet to check an action, a color, and a number, then return 1 or 0.
- 1. Open your worksheet: Open WPS Spreadsheet and select File > Open to choose your workbook.
- 2. Check the input cells: Enter or confirm the action in A9, the color in B9, and the number in C9.
- 3. Enter the formula: Select an empty cell and enter =IF(AND(A9="sell",B9="red",C9>0),1,0), then press Enter.
- 4. Fill other rows: Drag the result cell's fill handle down to apply the formula to additional rows.

Frequently Asked Questions
How do I return 1 only when all three conditions are true?
Enter =IF(AND(A9="sell",B9="red",C9>0),1,0) in a result cell. AND requires all three tests to be true.
How can I test for a negative value instead?
Replace C9>0 with C9<0 in the AND formula. The result will be 1 only when the other conditions also match.
What happens when the number is zero?
Zero is neither greater than zero nor less than zero. Add a C9=0 condition or a zero entry in your lookup table if it needs a specific result.
How do I assign results to several different combinations?
Create a table with a combined key for each scenario and its desired 1 or 0 result, then use VLOOKUP to find the key built from your input cells.




