How to Fix COUNTIF Not Accepting a Dynamic Array Formula in WPS Office Spreadsheets
Question details
The user needs to understand why COUNTIF and similar functions reject a nested dynamic array expression in WPS Spreadsheets and how to filter rows using an alternative LET and BYROW formula.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is trying to filter rows in a worksheet according to a dynamically calculated group list, but COUNTIF or SUMIF does not accept the nested array expression directly.
- Observed behavior
- COUNTIF, SUMIF, and similar functions return an error or fail when a calculated dynamic array is nested directly, even though the same array works when referenced through a spilled range.
Before editing your formula, verify that your WPS Office version supports dynamic array functions such as LET, FILTER, UNIQUE, and BYROW. Also make sure the ranges you reference do not contain merged cells that can block spill results.
Use LET and BYROW to Filter Rows by a Dynamic Array
Use this method when COUNTIF or SUMIF rejects a nested dynamic array and you need to return all rows that match at least one dynamically calculated group value.
COUNTIF, SUMIF, and similar functions expect a range reference for the criteria argument. A dynamic array expression calculated in memory is not a range, so nesting it directly fails even though referencing a spilled range may work. WPS Spreadsheets supports LET to store the calculated group list and BYROW to evaluate each row against that list.
Open the workbook in WPS Office Spreadsheets and click the worksheet cell where you want the filtered results to begin.
Type =LET(grpselect,TRANSPOSE(UNIQUE(FILTER($B$1:$B$30,($A$1:$A$30="GROUP")*($C$1:$C$30=TRUE)))),FILTER($G$1:$H$60,BYROW($F$1:$F$60=grpselect,LAMBDA(arr,OR(arr))))) and press Enter.
Replace $A$1:$A$30, $B$1:$B$30, $C$1:$C$30, $F$1:$F$60, and $G$1:$H$60 with your actual worksheet ranges.
Reference a Spilled Range Instead of Nesting the Dynamic Array
Use this alternative when you want to keep COUNTIF, SUMIF, or another range-based function and can place the dynamic array in a helper cell first.
Fix COUNTIF Dynamic Array Errors in WPS Spreadsheets
Enter the LET and BYROW formula in WPS Office Spreadsheets to filter rows by a calculated group list without COUNTIF rejecting the dynamic array. WPS supports the same dynamic array functions and .xlsx compatibility so you can fix the workbook directly.
- 1. Download WPS Office: Go to the WPS Office download page, download the free installer, and install WPS Office on your computer.
- 2. Open the workbook in WPS Spreadsheets: Launch WPS Office, click Spreadsheets, and open the .xlsx workbook that contains the COUNTIF formula.
- 3. Enter the dynamic filter formula: Click the result cell and type the LET and BYROW formula exactly as shown in the solution above, then press Enter.
- 4. Adjust and review the result: Update the range references to match your worksheet, then confirm that the spill range returns the expected filtered rows.
Frequently Asked Questions
Why does COUNTIF reject a nested dynamic array formula?
COUNTIF, SUMIF, and related functions expect a range reference for their arguments. A calculated dynamic array is an in-memory array, not a worksheet range, so nesting it directly can fail even when the same values work from a spilled range.
Can I make COUNTIF accept a dynamic array by using a spilled range reference?
Yes. Place the dynamic array formula in a helper cell, such as D1, and reference the spill with D1#. The spill reference is treated as a range, so COUNTIF and SUMIF can use it.
What formula should I use instead of COUNTIF to filter rows by multiple dynamic values?
Use LET to store the calculated group list and BYROW with LAMBDA and OR to test each row. Wrap the result in FILTER, for example: =LET(grpselect,TRANSPOSE(UNIQUE(FILTER($B$1:$B$30,($A$1:$A$30="GROUP")*($C$1:$C$30=TRUE)))),FILTER($G$1:$H$60,BYROW($F$1:$F$60=grpselect,LAMBDA(arr,OR(arr))))).
Why does the same dynamic array work only when it is spilled to a range?
WPS Spreadsheets and Excel evaluate a spilled range reference as a range object, whereas a nested array expression remains an array object. Range-based functions accept the range object but not the array object.
What should I check if LET, FILTER, UNIQUE, or BYROW is not available in WPS Office?
Update WPS Office to the latest version. These dynamic array functions require a current version that supports modern array calculation.
