Google-sheets – filter or sort Google Spreadsheet based on condition

I believe the issue is caused by filtered view sorting the column that contains input to a function. It can be reproduced in the following minimal example:

+---+-------+---+
|   |   A   | B |
+---+-------+---+
| 1 | input | 1 |
| 2 | 5     |   |
| 3 | 4     |   |
+---+-------+---+

where B1 is the formula =A2-A3. Create a filtered view for cells A1:B3 and use it to sort column A in ascending order. The result: B1 will display #REF error

The formula in cell A2 is referencing a range in the active filter that is not in the same row.

I can't really explain why this fails: my guess is that the logic of filtered view does not handle remapping cell references within the view.

But I can suggest an alternative approach: use FILTER and SORT, possibly putting results on different sheets for ease of sharing or reading. For example,

=SORT(FILTER(A2:A15, B2:B15=5), 1, TRUE)

keeps only the entries from A with 5 in column B, and presents them in ascending order. This formula could appear in another column (C,...), or on a different sheet.

You can filter using a regular expression. Use the options Filter by condition > Custom formula is...

=not(regexmatch(N:N, "(?i)facebook|pinterest|twitter"))

to see only the strings that do not contain one of the above.

Here N is the column you're filtering, and the regular expression means: the string contains match one of words facebook, pinterest, twitter, case-insensitive. And not is the negation of that.