Fix COUNTIFS #VALUE! Error in WPS Office Spreadsheets for One Excel Column
Question details
The user needs to resolve a COUNTIFS formula that returns #VALUE! in a single Excel column.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is counting rows that meet multiple criteria, but the formula evaluates the affected column incorrectly and returns an error.
- Observed behavior
- The COUNTIFS formula returns #VALUE! in that column.
Before editing, open the workbook in WPS Office Spreadsheets and make sure any external workbooks referenced by the formula are open or linked correctly. Then identify the cell with the COUNTIFS formula and note the ranges used in each argument.
Align COUNTIFS Criteria Ranges and Verify Workbook References
Use this solution first, because differently sized criteria ranges and broken external links are the most common causes of COUNTIFS #VALUE! errors.
COUNTIFS requires every criteria range to have the same number of rows and columns. If one range references a closed or broken workbook, the formula may also return #VALUE! instead of a count.
Open the workbook that contains the COUNTIFS formula in WPS Office Spreadsheets and select the worksheet where the #VALUE! error appears.
Click the error cell and view the formula in the formula bar. Confirm that criteria_range1, criteria_range2, and all other range arguments cover exactly the same number of rows and columns.
Click the Data tab and choose Edit Links. Make sure every referenced workbook is available or update broken links before recalculating.
Press Enter or click Formulas > Calculate Now to refresh the formula and verify that the #VALUE! error is gone.
Test the Problem Column and Rebuild the Formula in a Clean Range
Use this solution when the error is limited to one column or one workbook and the first check does not solve the issue.
Fix COUNTIFS Errors Directly in WPS Office Spreadsheets
WPS Office Spreadsheets supports the COUNTIFS function and Excel .xlsx/.xls files, so you can repair range mismatches, update broken links, and recalculate the formula without switching applications.
- 1. Download and install WPS Office: Download WPS Office from the official WPS site and install it, then open the workbook that contains the COUNTIFS error.
- 2. Open the affected workbook: In WPS Spreadsheets, click Menu > Open and browse to the .xlsx or .xls file containing the COUNTIFS formula.
- 3. Edit the formula: Click the cell with the #VALUE! error, check the formula bar, and adjust the COUNTIFS ranges so they have identical sizes.
- 4. Update links and recalculate: Click Data > Edit Links to check external references, then click Formulas > Calculate Now to refresh the results.
- 5. Save the corrected workbook: Press Ctrl+S to save the corrected workbook. The COUNTIFS formula should now return the expected count.
Frequently Asked Questions
Why does COUNTIFS return #VALUE! only in one column?
This usually happens because one criteria range in that column has different dimensions, contains incompatible data, or points to a broken external link. Click the formula cell in WPS Office Spreadsheets and compare each range size in the formula bar.
How do I check whether COUNTIFS ranges are the same size?
Select the cell with the formula and inspect the formula bar. Each criteria_range argument must cover the same number of rows and columns. You can also select a range inside the formula and press F9 to preview its values.
Can WPS Office open an Excel file that has COUNTIFS errors?
Yes. WPS Spreadsheets opens .xlsx and .xls files, and you can edit the COUNTIFS formula, update links under Data > Edit Links, and recalculate under Formulas > Calculate Now.
What should I do if COUNTIFS still returns #VALUE! after aligning ranges?
Test the column with COUNT, COUNTA, and ISNUMBER, then copy the data to a clean worksheet as values and rebuild the COUNTIFS formula from scratch.
Does COUNTIFS work with closed workbooks in WPS Office?
COUNTIFS can reference closed workbooks in some cases, but it is more reliable when the linked workbook is open. Use Data > Edit Links to update or break links before recalculation.
