FILTER in WPS Spreadsheet: Return Matching Rows with an Explicit Empty Result
Filter a small WPS table using a Boolean condition, include an empty-result message, and distinguish historical array entry from newer dynamic arrays.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: FILTER returns items whose corresponding condition is true. =FILTER(A2:A6,B2:B6="Open","No open items") returns the names attached to Open rows, or the chosen message when no rows match.
Availability and evidence scope
The official WPS FILTER lesson documents the behavior used here. It identifies WPS Office for Windows Personal. Reviewed October 19, 2026, this Chinese-language evidence does not establish an exact introduction build, every current edition, or English-global, Mac, Linux, web or mobile behavior. No new WPS execution or native screenshot was possible; the examples are independent controls or explicitly conditional fixtures.
Historical entry method versus newer releases
The 2021 lesson demonstrates selecting the complete result range, entering the formula and pressing Ctrl+Shift+Enter. Braces shown around an array formula are added by that workflow; do not type them as part of the formula. That historical method is the reproducible workflow described here.
The official Windows release log separately announces dynamic arrays in October 2023. Consequently, the older lesson’s no-dynamic-array statements are historical, not a claim that current WPS cannot expand array results. For a newer installation, confirm its array-entry behavior and available output space in that build. This guide does not import Excel spill rules, error codes, spill-reference operators or cross-workbook behavior into WPS. Monthly release notes do not establish an exact introduction build for FILTER.
Syntax and arguments
=FILTER(array,include,[if_empty])
This English signature normalizes the lesson’s documented sequence: source array, inclusion condition and optional empty-result text. Square brackets indicate an optional argument and are not typed.
array: the values to return, such as A2:A6 or a complete table A2:C6.include: a corresponding Boolean condition. Here B2:B6=“Open” supplies one decision per source row.if_empty: text to show when nothing matches. Supply it explicitly for a usable report.
The return range and condition must describe the same records. Do not select A2:A6 with B3:B7: those ranges have the same height but refer to different rows.
Example 1: an open-work list
Create this exact source, keeping the header outside the formula:
| Row | A: Item | B: Status | C: Hours |
|---|---|---|---|
| 2 | Draft | Open | 3 |
| 3 | Review | Done | 2 |
| 4 | Export | Open | 1 |
| 5 | Archive | Done | 4 |
| 6 | Check | Open | 2 |
Use the quick-answer formula in E2. Under the historical method, select E2:E4 first and confirm with Ctrl+Shift+Enter. The independently derived output is Draft, Export, Check, vertically in source order. The condition vector is TRUE, FALSE, TRUE, FALSE, TRUE.
For complete records, select a separate 3-by-3 output area under the historical method and enter:
=FILTER(A2:C6,B2:B6="Open","No open items")
The expected rows are Draft/Open/3, Export/Open/1 and Check/Open/2. Keeping each record intact prevents hours becoming detached from their items. This is a report view; it is not an instruction to delete the Done records.
Example 2: a report controlled by one cell
Enter Open in H1 and use =FILTER(A2:C6,B2:B6=H1,"No matching status") in a separate output area. With H1 equal to Done, the mathematical selection is Review/Done/2 and Archive/Done/4. With H1 equal to Waiting, no source row qualifies, so the requested fallback is No matching status.
A legacy selected array has a fixed output range: choose the appropriate dimensions again when reproducing a different fixture. Do not assume unused legacy output cells behave like a newer dynamic result. The source describes an empty-result calculation error without the third argument; supplying explicit fallback text avoids relying on its presentation.
Troubleshooting and design choices
- Match the criterion’s spelling to the source. Check spaces and inconsistent status labels before adding complex conditions.
- Keep formulas outside A2:C6, so the report does not replace its own inputs.
- If a legacy installation returns only one item, check the documented array-entry method and selected range.
- If a newer installation behaves differently, identify its version before applying the 2021 instructions unchanged.
- Expand both source and condition ranges when adding records; this fixture does not promise automatic table expansion.
For a distinct category list, see UNIQUE. To reorder a report by a numerical column, see SORT. Start with these separate operations before composing a larger formula; nested-array and spill-reference contracts are outside this guide.
References and verification
Expected outputs are derived from the explicit fixtures and documented operation. They are not results observed in an installed WPS build. Check the intended edition before relying on these formulas in a shared workbook.