Fix COUNTIFS #VALUE! Error Counting Positive Values Across Multiple Excel Columns in WPS
Question details
The user needs to count positive numeric values across several question columns in a spreadsheet only when rows match manager and relationship criteria, without receiving a COUNTIFS #VALUE! error.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is building a report from a raw data sheet with manager names in column A, relationship criteria in column D, and multiple question columns from E through T.
- Observed behavior
- A COUNTIFS formula returns a #VALUE! error when it combines single-column criteria ranges with a multi-column counting range, especially when entire-column references are used.
Open the workbook in WPS Office Spreadsheets and identify the exact raw data range, such as A2:T100, plus the columns holding the manager, relationship, and question values. Avoid whole-column references in the multi-column SUMPRODUCT formula.
Use SUMPRODUCT with Bounded Ranges to Count Positive Values Across Multiple Columns
Best for most workbooks because bounded ranges avoid the #VALUE! error and the formula is non-volatile.
SUMPRODUCT works row by row with arrays of the same size. It checks the manager criteria, the relationship criteria, and all question columns for positive values, then sums the rows that meet every condition.
Open the workbook in WPS Office Spreadsheets and click the cell where the #VALUE! error appears or where you want the count to appear.
Type or paste the following formula, adjusting the sheet name, row range, and criteria cell references to match your data: =SUMPRODUCT(('Raw Data'!$A$2:$A$100=$A2)*('Raw Data'!$E$2:$T$100>0)*('Raw Data'!$D$2:$D$100=C2))
Press Enter to commit the formula, then drag the fill handle down to apply it to the remaining manager rows.
Use SUM with COUNTIFS and OFFSET for Entire-Column References
Use this only when you must keep full column references; it works but is volatile and can reduce performance in large workbooks.
Count Positive Values Across Multiple Columns Without #VALUE! in WPS Office
Open your workbook in WPS Office Spreadsheets and replace the failing COUNTIFS with a bounded SUMPRODUCT formula. It checks the manager and relationship criteria row by row, then sums all question columns that are greater than zero.
- 1. Open your workbook: Launch WPS Office Spreadsheets and open the workbook that contains the raw data and the failing COUNTIFS formula.
- 2. Select the result cell: Click the cell where you want the positive-value count to appear, or where the #VALUE! error is currently displayed.
- 3. Enter the SUMPRODUCT formula: Type =SUMPRODUCT(('Raw Data'!$A$2:$A$100=$A2)*('Raw Data'!$E$2:$T$100>0)*('Raw Data'!$D$2:$D$100=C2)) and adjust the sheet name and row range to match your data.
- 4. Press Enter and copy down: Press Enter to get the first result, then use the fill handle to copy the formula down for the remaining criteria rows.
Frequently Asked Questions
Why does COUNTIFS return #VALUE! when counting across E:T with separate criteria columns?
COUNTIFS requires all range arguments to be the same size and can return #VALUE! when a multi-column counting range is combined with single-column criteria ranges, especially with entire-column references. Use a bounded SUMPRODUCT formula instead.
Can I still use COUNTIFS if I want to keep whole-column references?
Yes, you can combine SUM, COUNTIFS, OFFSET, and SEQUENCE to handle entire-column ranges, but the formula is volatile and may recalculate frequently. The bounded SUMPRODUCT method is preferred.
What does the SUMPRODUCT formula actually count?
The formula creates TRUE/FALSE arrays for the manager match, positive values in E:T, and relationship match, then multiplies and sums them. Each row that satisfies all conditions contributes 1 to the total.
Does WPS Spreadsheets support SUMPRODUCT and SEQUENCE?
WPS Spreadsheets supports SUMPRODUCT. SEQUENCE support depends on your version, so if SEQUENCE is unavailable, use the bounded SUMPRODUCT solution instead.
How do I avoid updating the bounded range every time the data grows?
You can convert the data range to a WPS table and use structured references, or extend the bounded range to a safe maximum row. Avoid using entire columns with the multi-column SUMPRODUCT because it may create extremely large arrays.
