Fix Excel IF Function #VALUE! Error for Multiple Ranges in WPS Office
Question details
The user needs a formula that returns Complete only when every cell in two separate ranges contains a value, but the current AND range comparison returns a #VALUE! error.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Building a status formula that checks multiple non-adjacent ranges for blank cells.
- Observed behavior
- The formula using AND with range comparisons returns #VALUE! instead of Complete or Incomplete.
Confirm the exact ranges and the total number of cells being checked. In the example, the expected non-empty count is 10 for C7:C10 and C13:C18.
Use COUNTA to Count Non-Blank Cells in Both Ranges
Use this when you know the expected number of non-empty cells and want a direct check that avoids array comparisons with AND.
COUNTA counts cells that are not empty. Comparing the total count with the expected number returns TRUE only when all cells in both ranges contain values.
Click the cell where you want Complete or Incomplete to appear.
Type =IF(COUNTA(C7:C10,C13:C18)=10,"Complete","Incomplete") and adjust the ranges and total count to match your data.
Press Enter to apply the formula. The cell shows Complete only when all 10 cells in both ranges contain values.
Verify No Blank Cells with COUNTIF and AND
Use this when you prefer an explicit blank-cell check; COUNTIF returns single values, so AND can compare them safely.
Fix the IF #VALUE! Error with WPS Office Spreadsheets
WPS Office Spreadsheets supports Excel-compatible functions such as IF, COUNTA, AND, and COUNTIF. Replace the faulty AND range-array formula with a direct non-blank count to return Complete or Incomplete without #VALUE!.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office and open the Excel file containing the formula error.
- 2. Select the result cell: Click the cell where the Complete/Incomplete result should appear.
- 3. Enter the COUNTA formula: Type =IF(COUNTA(C7:C10,C13:C18)=10,"Complete","Incomplete") and adjust the ranges and total count to your actual cells.
- 4. Press Enter and verify: Press Enter, then test by adding or removing values to confirm the result updates correctly.
- 5. Save in Excel format: Use Menu > Save As and choose Excel .xlsx format to preserve compatibility with Microsoft Excel.
Frequently Asked Questions
Why does the AND formula with range comparisons return a #VALUE! error?
AND is not designed to evaluate entire range arrays in this context. Use COUNTA to count non-blank cells across both ranges, or use COUNTIF to count blanks in each range and compare those single counts with AND.
Can I use this if the two ranges are not next to each other?
Yes. Both COUNTA and COUNTIF can accept multiple non-adjacent ranges. In the example, C7:C10 and C13:C18 are separated, and the formula checks them together.
What should I do if my ranges are a different size?
Adjust the expected count in COUNTA or add separate COUNTIF blank checks for each range. The expected total must equal the number of cells that should contain values.
Can I use COUNTIF with an asterisk to count non-empty cells?
COUNTIF with "*" only counts text values and can miss numbers. For a general non-empty count including numbers, use COUNTA, or use COUNTIF with blank-cell criteria and compare the result to 0.
How do I know the error is fixed?
After entering the replacement formula, remove or add values in the checked ranges. The status should switch between Complete and Incomplete without showing #VALUE!.
