Use Nested IF Instead of COUNTIFS for Four Excel Text Results in WPS Office
Question details
The user needs a formula that returns one of four text labels based on whether the values in A1 and B1 are greater than zero, using a nested IF approach instead of COUNTIFS.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is building a worksheet that classifies two inputs in A1 and B1 into one of four text results based on positive-value tests.
- Observed behavior
- The formula should return Contracted, Not Signed, Price Missing, or Not Contracted depending on whether each value is greater than zero.
Open the workbook in WPS Office Spreadsheets and confirm that A1 and B1 contain numeric values so the greater-than-zero comparisons behave as expected.
Use a Nested IF Function to Return One of Four Text Labels in WPS Office Spreadsheets
Use this solution when you need a simple, readable classification based on two cells and do not need to count matching rows.
COUNTIFS is designed to count records that satisfy multiple conditions. For returning a text label from the values in A1 and B1, a nested IF formula is more direct and easier to maintain.
Click cell C1, or the cell where you want the classification to appear.
Type or paste =IF(A1>0,IF(B1>0,"Contracted","Not Signed"),IF(B1>0,"Price Missing","Not Contracted")) into the selected cell.
Press Enter. The cell displays one of the four labels based on whether A1 and B1 are greater than zero.
Return Four Excel Text Labels with a Nested IF in WPS Office
Open your workbook in WPS Office Spreadsheets, enter a nested IF formula, and classify values in A1 and B1 as Contracted, Not Signed, Price Missing, or Not Contracted without COUNTIFS.
- 1. Open the workbook: Launch WPS Office Spreadsheets and open the workbook that contains A1 and B1.
- 2. Select the output cell: Click cell C1 or the first cell where you want the text label to appear.
- 3. Enter the formula: Type =IF(A1>0,IF(B1>0,"Contracted","Not Signed"),IF(B1>0,"Price Missing","Not Contracted")) in the formula bar.
- 4. Apply the result: Press Enter to display the classification, then use the fill handle to copy the formula down if you have multiple rows.
Frequently Asked Questions
Can COUNTIFS return one of four text labels instead of counting?
No. COUNTIFS returns a count of rows that meet conditions. Use a nested IF or IFS formula to return a text classification from two cells.
What result does the formula show when both A1 and B1 are greater than zero?
It shows Contracted, because the outer IF finds A1 greater than zero and the inner IF finds B1 greater than zero.
What happens when A1 is greater than zero but B1 is zero or negative?
It returns Not Signed. If A1 is not greater than zero and B1 is greater than zero, it returns Price Missing. If neither is greater than zero, it returns Not Contracted.
Can I use this formula in Microsoft Excel as well as WPS Office?
Yes. The nested IF formula works in WPS Office Spreadsheets and Microsoft Excel, and the workbook remains XLSX-compatible.
