6

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.

3
  • 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

2 Answers 2

4

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"))
3
  • 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. Mar 6, 2016 at 3:57
  • What do Col1 & Col2 refer to?
    – Giraldi
    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
1

This is bit better:

=FILTER(A1:A20,A1:A20 > E1)
1
  • 1
    what is the value of E1, how does this work? Jul 31, 2020 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.