Fileter between two numbers

Question

Fileter between two numbers

Andy Stewart 0

I have the following formula below. It all works well, however I would like some more control.

In (CHOOSECOLS(x,12)<12) I have it set to filter to <12. I would like it to be <3 and >12.

Would love some help. Thank you.

=LET(x,VSTACK(

IF(COUNTA('Sales Pipeline Data'!E3:E5079)>0,OFFSET('Sales Pipeline Data'!E3,0,0,COUNTA('Sales Pipeline Data'!E3:E5079),15),{"","","","","","","","","","","","","","",""})

),y,SORT(FILTER(CHOOSECOLS(IF(x="","",x),1,12),(CHOOSECOLS(x,1)<>"")(CHOOSECOLS(x,7)<>"")(CHOOSECOLS(x,9)="")*(CHOOSECOLS(x,12)<12)),2,-1),IF(IFERROR(ROWS(y),0)<>0,y,""))

0 comments

1 answer

Your answer

Answer 1

Marcin Policht 88,075 MVP Volunteer Moderator

Looks like your condition only checks one side: (CHOOSECOLS(x,12)<12). To make it “less than 3 OR greater than 12”, you should explicitly combine two logical tests with a plus + (which acts like OR in array formulas).

Replace that part with:

((CHOOSECOLS(x,12)<3)+(CHOOSECOLS(x,12)>12))

So your full formula becomes:

=LET(x,VSTACK(
IF(COUNTA('Sales Pipeline Data'!E3:E5079)>0,OFFSET('Sales Pipeline Data'!E3,0,0,COUNTA('Sales Pipeline Data'!E3:E5079),15),{"","","","","","","","","","","","","","",""})
),
y,SORT(
FILTER(
CHOOSECOLS(IF(x="","",x),1,12),
(CHOOSECOLS(x,1)<>"")*
(CHOOSECOLS(x,7)<>"")*
(CHOOSECOLS(x,9)="")*
((CHOOSECOLS(x,12)<3)+(CHOOSECOLS(x,12)>12))
),
2,-1
),
IF(IFERROR(ROWS(y),0)<>0,y,"")
)

The key idea is that * acts like AND, while + acts like OR in these array conditions.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Andy Stewart 0 Reputation points

2026-04-29T11:35:00.25+00:00

Hi Marcin.

Thank you very much for this.

All my fault. I would like the result to show all the cells where the number falls between 3 and 12.

Could you help me with that one please?

Andy
Marcin Policht 88,075 Reputation points MVP Volunteer Moderator

2026-04-29T11:42:13.02+00:00
If you want values between 3 and 12, you need an AND condition instead of OR, so you use * and check both bounds.

Replace your condition with:

(CHOOSECOLS(x,12)>=3)*(CHOOSECOLS(x,12)<=12)

Full formula with the same structure:

=LET(x,VSTACK( IF(COUNTA('Sales Pipeline Data'!E3:E5079)>0,OFFSET('Sales Pipeline Data'!E3,0,0,COUNTA('Sales Pipeline Data'!E3:E5079),15),{"","","","","","","","","","","","","","",""}) ), y,SORT( FILTER( CHOOSECOLS(IF(x="","",x),1,12), (CHOOSECOLS(x,1)<>"")* (CHOOSECOLS(x,7)<>"")* (CHOOSECOLS(x,9)="")* (CHOOSECOLS(x,12)>=3)*(CHOOSECOLS(x,12)<=12) ), 2,-1 ), IF(IFERROR(ROWS(y),0)<>0,y,"") )

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin
Andy Stewart 0 Reputation points

2026-04-29T11:50:29.9533333+00:00

Done it. Thank yo for the help. Made me understand what to do.
Marcin Policht 88,075 Reputation points MVP Volunteer Moderator

2026-04-29T12:33:28.6133333+00:00

You're welcome. Remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Share via

Fileter between two numbers

1 answer

Your answer