I am using a filter function to pull out numbers from a column. I have one function which is grabbing numbers over 101 and it is outputting them perfectly in two vertical cells this formula:

=(filter(J2:J21, J2:J21>=101))

However when I write this same formula for numbers under 101 which is:

=(filter(J2:J21, J2:J21<=101))

it outputs the numbers multiple cells away from each other and the distance matches how far away they were in the original list pulled from.

I find this odd since the only difference is switching the > to a <

Can anyone help me on this? enter image description here enter image description here


1 Answer 1


Welcome! Please try =FILTER(J2:J21, (J2:J21<=101)*(J2:J21>0))

or =FILTER(J2:J21, (J2:J21<=101)*(J2:J21<>""))

(empty cells are less than 101, right?)

  • That worked!!! Thank you so much! Dec 5, 2022 at 17:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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