How to Return a Value Based on Two YES or NO Cells in Excel
Question details
The user needs an Excel formula that outputs 5,000 when two designated cells contain 'YES', 3,000 when only one cell contains 'YES', and an optional/empty result when neither contains 'YES'.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Assigning dynamic numeric values or rewards based on a tally of text-based conditions (YES/NO) in a spreadsheet.
- Observed behavior
- Needs a logical formula to accurately calculate and display the correct tier value depending on the total count of 'YES' responses.
Ensure the cells you are evaluating contain clean data without hidden leading or trailing spaces, as formulas looking for exact text matches like 'YES' are sensitive to extra characters.
Use the MIN and SUMPRODUCT Functions
A compact and efficient formula that counts the 'YES' values, multiplies them, and caps the maximum output value.
This method uses the double negative (--) to convert TRUE/FALSE arrays into 1s and 0s. SUMPRODUCT adds them up, multiplying the count by 3,000. If both are 'YES', it results in 6,000, but the MIN function caps the final output at 5,000.
Click on the cell where you want the final value (3000, 5000, or 0) to be displayed.
Type the formula `=MIN(SUMPRODUCT(--(A1:B1="Yes"))*3000,5000)`, adjusting the range `A1:B1` to match the two cells you are checking.
Press Enter. The cell will now calculate the correct value based on the presence of 'Yes'.
Use COUNTIF with Nested IF Functions
A straightforward logical approach using standard IF and COUNTIF functions, which is highly intuitive and works in all versions of Excel.
Use LET and CHOOSE Functions (Microsoft 365 Syntax)
A highly readable method utilizing modern array functions like LET to declare variables, ideal for newer Excel versions.
Easily Manage Complex Logical Formulas with WPS Office
WPS Spreadsheet fully supports advanced functions like SUMPRODUCT, COUNTIF, and LET. You can easily build, test, and troubleshoot conditional formulas to automate your data tasks with an interface that feels instantly familiar.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the YES/NO data.
- 2. Enter the Formula: Click the target cell and paste your preferred formula, such as `=MIN(SUMPRODUCT(--(A1:B1="Yes"))*3000,5000)`.
- 3. Apply and Drag: Press Enter to get the result. Use the fill handle at the bottom right corner of the cell to drag and apply the formula to the rest of the column.
Frequently Asked Questions
Why is my formula returning a #VALUE! error?
This usually happens if the range referenced in mathematical functions like SUMPRODUCT contains existing error values. Ensure your source cells (e.g., A1 and B1) contain valid text or are completely empty.
How can I change the default output when there are no 'Yes' values?
If you are using the Nested IF or CHOOSE method, you can replace the blank `""` with a custom text string like `"None"` or a number like `0`. For example: `=IF(COUNTIF(A1:B1,"Yes")=2, 5000, IF(COUNTIF(A1:B1,"Yes")=1, 3000, "None"))`.
Can I check for both 'YES' and 'NO' explicitly?
Yes. If you need to ensure the other cell specifically says 'NO' rather than just being blank or containing random text, you can use the `COUNTIFS` function combined with `AND` logic to strictly evaluate both cell conditions before assigning a value.
