# What's the formula to select a range excluding a sub-range?

I want to say A1:A60 excluding A20:A30. Is there a minus operator for use with ranges, in Google Sheets?

I know that you can take the union of two ranges, like {A1:A19;A31:A60}. I would like to know if there's an operator that excludes one range from another.

• Is it always the same range to exclude or is there some conditions within the range that we can use to define the rule for exclusion? Mar 5, 2016 at 6:13
• Good question. I think you have to get tricky as I don't think there's a SUBTRACT or EXCEPT function Mar 5, 2016 at 7:19
• For now, it's always the same range, @AuriellePerlmann , to keep it simple. Mar 6, 2016 at 3:56

With the following formula it is possible to exclude a certain range:

``````=ARRAYFORMULA(QUERY({ROW(A1:A60), A1:A60}, "SELECT Col2 WHERE Col1<20 OR Col1>30"))
``````
• While this is so complex as to not be worth it, it looks like Sheets doesn't support anything better, so I've accepted this answer. Mar 6, 2016 at 3:57
• What do `Col1` & `Col2` refer to? Jan 30 at 20:59
• @giraldi Col1 is pointing to the first range, being the rows. Col2 point towards the second range. See help: support.google.com/docs/answer/6208276 Jan 30 at 21:37

This is bit better:

``````=FILTER(A1:A20,A1:A20 > E1)
``````
• what is the value of E1, how does this work? Jul 31, 2020 at 5:17