Troubleshoot COUNTIFS Formula That Fails for One Column in WPS Spreadsheets
Question details
The user needs to identify why a COUNTIFS percentage formula fails for column BM while working for other columns, and how to correct the formula or data.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user calculates the percentage of male students whose endline score exceeds the baseline score in a matched student database, and the formula works for most columns but not column BM.
- Observed behavior
- The COUNTIFS formula returns an unexpected result or error for column BM, despite identical formatting and the presence of NA values; row 42 is suspected.
Before editing, note the current formula and select column BM to inspect sample values in the formula bar, especially row 42. Check whether NA is literal text or an #N/A error, because this changes the COUNTIFS criteria.
Inspect and clean column BM values before changing the formula
Use this first when only one column fails; it finds hidden spaces, blank strings, text-formatted numbers, and the suspected row 42 value.
COUNTIFS compares values exactly, so a cell that looks like a number but is actually text, or contains a leading space, can fail the >0 or <>NA criteria. Column BM may also contain blank strings returned by formulas, which are not the same as an empty cell.
In WPS Spreadsheets, click the BM column header, then press Ctrl+1. Confirm the category is set to Number or General, and click OK.
Press Ctrl+G, type BM42, and press Enter. Look in the formula bar for a leading apostrophe, leading or trailing spaces, or a small green error indicator.
Go to Home > Find & Select > Go To Special. Select Constants, check Text only, and click OK to highlight values that need conversion to numbers.
Use helper cells with =TRIM(BM2) and =CLEAN(BM2) to detect extra characters. If formulas return blank strings, clear them or replace them with actual blanks before rechecking the COUNTIFS formula.
Rewrite COUNTIFS criteria and exclude NA from the denominator
Use this when column BM contains numeric values and NA is literal text, but the formula still returns the wrong percentage due to missing denominator filters.
Verify baseline and endline column references for the failing column
Use this if the formula itself may be pointing to the wrong baseline or endline columns for BM.
Fix the COUNTIFS Column BM Failure in WPS Spreadsheets
Open your workbook in WPS Spreadsheets, inspect column BM for hidden spaces or text numbers, rewrite the COUNTIFS criteria to exclude NA values, and wrap the formula with IFERROR to avoid zero-denominator errors.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, open the Excel workbook containing the Matched database sheet, and navigate to the sheet with the COUNTIFS formula.
- 2. Inspect column BM values: Click the BM column header, press Ctrl+1 to verify Number format, and check row 42 in the formula bar for leading apostrophes, spaces, or text-formatted numbers.
- 3. Rewrite the COUNTIFS formula: In the result cell, enter =IFERROR(COUNTIFS('Matched database'!$BM:$BM,">0",'Matched database'!$AG:$AG,"Boy")/COUNTIFS('Matched database'!$AG:$AG,"Boy",'Matched database'!$BM:$BM,"<>NA"),0).
- 4. Recalculate and save: Press Enter, compare the BM result with a working column, and save the file as .xlsx to keep it compatible with Microsoft Excel.
Frequently Asked Questions
Why does COUNTIFS work for other columns but fail on column BM?
Most often, column BM contains values that do not match the criteria exactly: text-formatted numbers, leading or trailing spaces, blank strings, or the value in row 42. Select column BM, press Ctrl+1 to check the format, and inspect BM42 in the formula bar.
How do I exclude NA values from the denominator in WPS Spreadsheets?
If NA is literal text, add a condition such as 'Matched database'!$BM:$BM,"<>NA" to the COUNTIFS denominator. If the cells contain the #N/A error, use a numeric condition like ">0" or count only numeric cells instead.
How can I prevent a #DIV/0! error when the denominator is zero?
Wrap the whole percentage formula in IFERROR, for example =IFERROR(your_formula,0), or use =IF(COUNTIFS(...)=0,0,your_formula). Enter this in the formula bar and press Enter.
How do I find text numbers or hidden spaces in one column?
Select the column, go to Home > Find & Select > Go To Special, choose Constants and Text, then click OK. Use helper formulas =TRIM(BM2) and =CLEAN(BM2) to reveal spaces and non-printing characters.
Does WPS Spreadsheets support COUNTIFS when I open an Excel file?
Yes. WPS Spreadsheets opens and saves .xlsx workbooks and supports COUNTIFS. You can edit the formula, clean the data, and save the file back in Excel-compatible format.
