Filter with an AND or OR Criteria Range in LibreOffice Calc

An Advanced Filter keeps its conditions in worksheet cells. Put conditions on one row for AND; use separate criteria rows for OR. Copying results to a dedicated area makes the result easier to audit without treating a temporarily hidden source row as a deleted record.

This recipe follows Calc Guide 26.2 and current Help labeled 26.8, checked 19 October 2026. It uses the English standard-menu interface and simple literal text plus numeric comparisons. The example’s expected ID sets are independently enumerated; no native filter execution is claimed.

1. Enter a complete source and keep outputs separate

Create a sheet named Review and enter this table in A1:D9. Enter Amount as numbers. Do not insert blank rows inside the source.

ID	Region	Status	Amount
R-01	North	Ready	80
R-02	North	Hold	130
R-03	South	Ready	120
R-04	West	Ready	160
R-05	North	Ready	150
R-06	South	Hold	90
R-07	West	Hold	110
R-08	North	Ready	100

There are eight records, with total amount 940. Put =SUM(D2:D9) in F10 as a source control. Reserve F1:I3 for criteria, K1:N9 for the first output, and P1:S9 for a later output. Leave these output areas empty. Save an ODS checkpoint.

Copy A1:D1 into F1:I1. These labels must match the source labels exactly. Do not use friendlier alternatives such as Area for Region. In the criteria blocks below, “blank” means leave the cell genuinely empty; do not type that word or a space.

2. Express one AND group

Set F2:I2 like this:

F2: ID	G2: Region	H2: Status	I2: Amount
blank	North	Ready	>=100

Enter >=100 as criterion text, not as a formula beginning with an extra equals sign. The intended rule is Region North and Status Ready and Amount at least 100. R-05 and R-08 qualify; R-01 fails the amount threshold, while R-02 fails the status condition.

1. Select the complete source A1:D9.
2. Choose Data → More Filters → Advanced Filter.
3. In Read filter criteria from, enter $Review.$F$1:$I$2, including the header row and only this one criteria row.
4. Expand Options. Enable Range contains column labels and Copy results to. Set the destination to $Review.$K$1.
5. Leave Regular expression, No duplication and Keep filter criteria off. Leave Case sensitive off for this consistently capitalized fixture.
6. Click OK and inspect the destination and source.

These controls are documented in Advanced Filter help. No duplication is off because this exercise selects records; it is not a deduplication task.

Expect K1:N3 to contain the four headers and two complete records, R-05/North/Ready/150 and R-08/North/Ready/100, in source order. =SUM(N2:N3) is 250. Check all fields against A1:D9; count and sum alone cannot detect a torn record. Confirm the original eight IDs and source control 940 remain intact.

3. Add an OR alternative

Keep the first criteria row unchanged. In F3:I3 put blank, South, Hold, blank. The complete criterion is now:

• North AND Ready AND Amount >=100
• OR South AND Hold, with no amount threshold

Select A1:D9 again and reopen Advanced Filter. Extend Read filter criteria from to $Review.$F$1:$I$3. Use the same options, but copy to $Review.$P$1, a fresh destination. The first output remains as a comparison snapshot.

Expect P1:S4 to contain the headers followed by R-05 (150), R-06 (90) and R-08 (100), total 340. R-03 is South but Ready, so it must not be included. R-06 qualifies despite its amount being below 100 because that threshold belongs to the other criteria row.

The Calc database guide explicitly describes same-row AND, different-row OR and ignored empty criterion cells. Do not extend the criteria selection over unrelated cells or extra blank rows “just in case.” Select the exact intended criteria rectangle.

4. Change, rerun and eliminate stale output

Change I2 from >=100 to >=140. This does not mean that the already-copied output has refreshed. Clear the old output contents in P1:S9, leaving source and criteria untouched. In Calc, use Sheet → Clear Cells and choose the appropriate contents, or use the clear-contents command on the selected output block; do not delete whole worksheet rows.

Rerun Advanced Filter against A1:D9 and F1:I3, copying to P1. Expected IDs are now R-05 and R-06, total 240. Check that the previous R-08 output is gone. A shorter new result pasted over a longer old result is dangerous unless you deliberately clear and audit the destination.

Restore I2 to >=100, clear P1:S9 again and rerun: expect R-05, R-06, R-08 and 340. Save and reopen the ODS, inspect the criteria strings, and rerun once more before relying on the report. This exercises the saved instructions as well as the saved result.

Advanced boundary and source-change tests

For an exclusive threshold test, use >100 in I2 with the first AND group only, F1:I2. Output to a cleared K1:N9. Expect only R-05 and 150; R-08 at exactly 100 must be excluded. Restore >=100 and verify both IDs return.

Then change R-01’s source amount from 80 to 105. The source total becomes 965. With the restored first AND group and a newly cleared output, expect R-01, R-05 and R-08, totaling 355. Restore 80, rerun and check the original 940 source total and 250 output total. Keep every source edit reversible.

Troubleshooting

• Wrong rows match: inspect the exact criteria range first. A condition placed on the second row is an OR alternative, not another AND condition.
• No rows match: compare header spelling and numeric Amount types. A space in an apparently empty criterion is not the same as a blank cell.
• Unexpected text matches: inspect Calc → Calculate’s whole-cell search setting and the filter’s regular-expression option. This fixture uses distinct simple labels, not wildcards or regex patterns.
• Old records remain below the result: clear the dedicated output rectangle and rerun. Do not remove data from the source to make the report look right.
• Source rows appear hidden: use Data → More Filters → Reset Filter before another source audit. Confirm the Copy results to setting on the next run.

For duplicate removal, use the separate deduplication tutorial. For this workflow, the acceptance evidence is the complete ID list and field-by-field match, not the absence of a filter error. Native execution and saved-workbook checks for this fixture remain unexecuted; verify them in your build.