Arrays · eight-software comparison

FILTER with two conditions

Compare dynamic FILTER arrays, two-condition logic, if-empty behaviour and legacy calculation workarounds across spreadsheet software.

Task

Return Amount values where Region is North and Status is Open. Matching values are 125 and 75.

Expected output

125, 75 (two-row result)

Compatibility trap: Zoho FILTER accepts additional condition arguments rather than an if_empty argument in the same position as LibreOffice and ONLYOFFICE.

One shared five-row test dataset

The same source rows make results easier to compare. Use the CSV in any supported spreadsheet application; the ODS workbook also includes LibreOffice formula tests.

Sample data (.csv) ↓Calc test workbook (.ods) ↓

Shared formula comparison fixture (columns A through F)
Region (A)	Status (B)	Amount (C)	SKU (D)	Label (E)	Units (F)
North	Open	125	SKU-101	Alpha	2
South	Closed	80	SKU-102	Beta	1
North	Open	75	SKU-103	Gamma	3
North	Closed	50	SKU-104	Delta	4
South	Open	200	SKU-105	Epsilon	1

Formula syntax and feature differences

“Documented” rows cite official product help, but were not run in the destination software. A formula shown here is a migration candidate, not a guarantee of identical behaviour or file interoperability.

LibreOffice Calc

Native tested

Version: Introduced 24.8; array-entry/placement behaviour differs from dynamic spill tools.

Arguments: FILTER(range;criteria;[if_empty])

=FILTER(C2:C6;(A2:A6="North")*(B2:B6="Open");"No rows")

Boolean vectors multiplied act as AND. Native test used SUM(FILTER(...)) = 200, not a visual spill verification.

Official documentation ↗

Apache OpenOffice Calc

Alternative

Arguments: Data > Filter or helper column

Use a standard filter or create a helper formula; a dynamic FILTER formula is not established by the official Calc guide.

Official documentation ↗

Gnumeric

Not researched

Do not conflate Gnumeric’s data filtering commands with a verified FILTER worksheet function.

Official documentation ↗

Calligra Sheets

Alternative

Arguments: Data → Filter or helper column

Use the Data menu or a helper column instead of assuming a FILTER formula exists.

Official documentation ↗

PlanMaker

Not researched

Array features are documented, but no native FILTER formula syntax was verified from this patch’s checked pages.

Official documentation ↗

ONLYOFFICE Spreadsheet Editor

Documented

Arguments: FILTER(array,include,[if_empty])

=FILTER(C2:C6,(A2:A6="North")*(B2:B6="Open"),"No rows")

The last argument is an empty-result fallback. Check array output space.

Official documentation ↗

Apple Numbers

Documented

Arguments: FILTER(array,include,[if-empty])

=FILTER(C2:C6,(A2:A6="North")*(B2:B6="Open"))

Numbers FILTER function documented; combined vector expression is an editorial transfer example, not runtime-tested.

Official documentation ↗

Zoho Sheet

Documented

Arguments: FILTER(range;condition;[condition1];…)

=FILTER(C2:C6;A2:A6="North";B2:B6="Open")

Zoho documents multiple condition arguments; do NOT paste a LibreOffice if_empty value as its third argument.

Official documentation ↗

What changes when you migrate?

The expected returned vector is 125 then 75. The LibreOffice runtime assertion checks SUM=200; it does not prove visual spill layout or destination placement.

The third argument illustrates semantic differences: “No rows” is an empty-result fallback in LibreOffice and ONLYOFFICE, but a second condition slot in Zoho.

For older tools, use the spreadsheet’s row filters or an explicit helper flag. Record whether hidden rows or formulas drive the result.

Actual LibreOffice calculation evidence

Formula tested in LibreOffice Calc 25.2.3.2. The published manifest provides the exact input formula, checked result, and whether the test validates a scalar result rather than array spill layout.

Test ID: filter. See calculation results (JSON).

Why the third argument matters

The two North/Open rows hold 125 and 75. A FILTER expression should return those entries in that order. The downloaded LibreOffice workbook proves SUM(FILTER(...))=200; it does not assert that the spill area is rendered as two cells in every release or import path.

In LibreOffice and ONLYOFFICE, the third argument is documented as an optional value to use when no rows match. In Zoho Sheet, the help signature is FILTER(range;condition;[condition1];...): the third argument is an additional condition. Blindly copying a LibreOffice empty-result string into a Zoho formula therefore changes the formula’s logic.

Array behaviour needs its own tests

Reserve enough empty destination cells for a spilled result.
Test a no-match scenario explicitly, recording whether it returns an error, empty array or fallback.
Test a source range containing blanks and headers, rather than assuming filtering handles them identically.
In a legacy engine, use the application’s built-in data filter or a row-flag helper instead of pretending modern dynamic arrays work unchanged.