Excel IF Formula Based on Values in Two Columns in WPS Office
Question details
The user needs an Excel-compatible formula in column D that returns a combined completion status based on text values in columns B and C.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet has status values in columns B and C, such as Yes, N/A, and Not Completed, and column D must show the resulting status.
- Observed behavior
- The result should be Completed, Partially Completed, or Not Completed depending on the exact combination of values.
Before writing the formula, confirm that columns B and C contain the exact status text Yes, N/A, or Not Completed with no leading or trailing spaces, and decide which column will hold the result.
Use an IF, OR, and AND formula to return the status in column D
Best when you need a compact, fill-down formula that handles the exact Yes, N/A, and Not Completed combinations.
The formula first checks whether the row qualifies as Completed. If B2 is Yes, or if B2 is N/A and C2 is Yes, the formula returns Completed. It then checks whether both cells are Not Completed; if true, it returns Not Completed. Any other combination returns Partially Completed.
Click D2 in your WPS Spreadsheets worksheet so that the first result will appear next to the data in columns B and C.
Type or paste =IF(OR(B2="Yes",AND(B2="N/A",C2="Yes")),"",IF(AND(B2="Not Completed",C2="Not Completed"),"Not ","Partially "))&"Completed" into the formula bar and press Enter.
Drag the fill handle from the bottom-right corner of D2 down through all rows that contain values in columns B and C.
Create a two-column IF formula in WPS Spreadsheets
WPS Spreadsheets supports Excel-style IF, OR, and AND functions, so you can build this status formula directly in a familiar spreadsheet interface and keep working with .xlsx files.
- 1. Open the workbook: Launch WPS Office, click Open, and select the Excel workbook that contains columns B and C.
- 2. Enter the formula: Click D2, type =IF(OR(B2="Yes",AND(B2="N/A",C2="Yes")),"",IF(AND(B2="Not Completed",C2="Not Completed"),"Not ","Partially "))&"Completed", and press Enter.
- 3. Apply to the remaining rows: Drag or double-click the fill handle in D2 to fill the formula down for the rest of the dataset.
Frequently Asked Questions
What does the formula return when B2 is Yes?
It returns Completed because the OR condition is true, so the IF true part is an empty string and only "Completed" is appended.
What does the formula return when both B2 and C2 are Not Completed?
It returns Not Completed because the second IF condition uses AND to detect that exact pair.
Why is my formula returning Partially Completed unexpectedly?
The formula returns Partially Completed for any combination not covered by the Yes/N/A or both Not Completed conditions. Check that B and C contain the exact expected text values.
Can I use this formula in both WPS Spreadsheets and Microsoft Excel?
Yes. WPS Spreadsheets supports .xlsx workbooks and the same IF, OR, and AND functions, so the formula is cross-compatible.
How do I apply the formula to all rows?
After entering it in D2, drag the fill handle down column D or double-click the fill handle to fill adjacent rows automatically.
