How to Keep Excel IF Results Blank When Input Cells Are Empty
Question details
The user needs an Excel-compatible WPS Spreadsheets formula that returns a blank cell when the entire L6:W6 range is empty, then returns 2 if all cells in that range equal 2, otherwise returns 1.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is building or editing a worksheet formula that evaluates the range L6:W6 and expects a blank result when no input values have been entered.
- Observed behavior
- The original IF formula returns 1 when L6:W6 is empty because blank cells do not satisfy the all-2 condition and the formula falls through to its final result.
Open the workbook in WPS Spreadsheets and confirm the target input range contains exactly 12 cells. If your actual range has a different number of cells, replace 12 with the correct cell count in the formulas below.
Use COUNTBLANK to Return a Blank Result for an Empty Input Range
Use this solution when you want the formula to check whether the entire L6:W6 range is blank before applying the all-2 rule.
The COUNTBLANK function counts empty cells in a range. If all 12 cells in L6:W6 are blank, the formula returns an empty string instead of continuing to the IF logic.
Click the cell in WPS Spreadsheets where you want the result to appear, such as the cell currently containing the original IF formula.
Type or paste =IF(COUNTBLANK(L6:W6)=12,"",IF(AND(L6=2,M6=2,N6=2,O6=2,P6=2,Q6=2,R6=2,S6=2,T6=2,U6=2,V6=2,W6=2),2,1)).
Press Enter. Empty input cells now show a blank cell, all 2 values show 2, and any other entered values show 1.
Use COUNTIFS as a Compact Alternative Formula
Use this equivalent formula if you prefer a shorter COUNTIFS-based approach.
Fix the Blank-Cell IF Formula in WPS Spreadsheets
Use WPS Spreadsheets to directly edit the formula so it returns blank for empty input cells, returns 2 when all L6:W6 cells equal 2, and returns 1 otherwise. The formula uses standard functions that are compatible with Microsoft Excel.
- 1. Open WPS Spreadsheets: Launch WPS Office and open the workbook that contains the formula in WPS Spreadsheets.
- 2. Select the formula cell: Click the cell where the IF formula should return the result.
- 3. Insert the corrected formula: Type the recommended COUNTBLANK formula into the selected cell or formula bar.
- 4. Press Enter to apply: Press Enter to calculate and confirm that blank input cells stay blank.
- 5. Save the workbook: Click the Save icon or use Ctrl+S to keep the corrected formula.
Frequently Asked Questions
Why does my Excel IF formula return 1 when L6:W6 is blank?
Because the condition AND(L6=2,...,W6=2) is not true for blank cells, so execution continues to the final value 1. Add a COUNTBLANK or COUNTIFS blank-range check at the start of the formula.
Can I use COUNTIFS instead of COUNTBLANK?
Yes. Use =IF(COUNTIFS(L6:W6,"")=12,"",IF(COUNTIFS(L6:W6,2)=12,2,1)). It returns blank when all 12 cells are empty, 2 when all are 2, and otherwise 1.
What should I change if my input range has more or fewer than 12 cells?
Replace 12 with the number of cells in your actual range. For example, count the cells in the new range and compare the blank-cell count to that total.
Does this formula work in both WPS Spreadsheets and Microsoft Excel?
Yes. COUNTBLANK and COUNTIFS are standard functions in both applications, so the formula is compatible with .xlsx workbooks.
How can I show a truly empty cell instead of an empty string?
A formula cannot return a true blank value, but returning "" makes the cell appear blank. You can combine it with display or formatting options if needed.
