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. – Vaddadi Kartick 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"))
| improve this answer | |
  • 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. – Vaddadi Kartick Mar 6 '16 at 3:57

This is bit better:

=FILTER(A1:A20,A1:A20 > E1)
| improve this answer | |

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.