Formula Errors

Excel Formula for Three Conditions Returning 1 or 0

Adam DavisAdam Davis Nov 18, 2026 870 views

Question details

Create a formula that evaluates three variables and returns 1 or 0.

Excel Formula for Three Conditions Returning 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.
Before you start

Identify the cells containing the action, color, and number, and decide which combinations should return 1. The examples below use A9, B9, and C9.

Solution 1Recommended

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.

1
Enter the formula

Select an empty result cell and enter =IF(AND(A9="sell",B9="red",C9>0),1,0).

2
Check the result

Press Enter. The formula returns 1 only when A9 is sell, B9 is red, and C9 is greater than zero; otherwise, it returns 0.

3
Apply it to other rows

Drag the result cell's fill handle down to evaluate the corresponding cells in other rows.

Use IF and AND for one matching combination
Change the conditions: Replace the text values or comparison operator to match your rules. For example, C9<0 tests for a negative number; C9=0 tests for zero.
WPS Spreadsheet

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. 1. Open your worksheet: Open WPS Spreadsheet and select File > Open to choose your workbook.
  2. 2. Check the input cells: Enter or confirm the action in A9, the color in B9, and the number in C9.
  3. 3. Enter the formula: Select an empty cell and enter =IF(AND(A9="sell",B9="red",C9>0),1,0), then press Enter.
  4. 4. Fill other rows: Drag the result cell's fill handle down to apply the formula to additional rows.
Evaluate three conditions in a single formula.Open and edit Microsoft Excel XLSX and XLS files.Use a familiar spreadsheet interface to fill formulas down a column.Get started with WPS Office for free.
microsoft office alternative - wps office

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.