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? – Aurielle Perlmann Mar 5 '16 at 6:13
  • Good question. I think you have to get tricky as I don't think there's a SUBTRACT or EXCEPT function – Raystafarian Mar 5 '16 at 7:19
  • For now, it's always the same range, @AuriellePerlmann , to keep it simple. – Kartick Vaddadi Mar 6 '16 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"))
  • 1
    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. – Kartick Vaddadi Mar 6 '16 at 3:57

This is bit better:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.