FILTERING QUESTION

[meppwc]

I have a spreadsheet where as columns L, M, N, O all have numbers in them. There are over 6000 rows.
I am looking for a quick way to filter those columns to where I can see only rows where the value in any of the columns mentioned, are less that 20.

 

[Phuoc]

Try this:

=FILTER(L2:O10000,MMULT(--(L2:O10000<20),{1;1;1;1}),"")

 

[meppwc]

I placed in cell Q1 where no data exists in the column........the result was #CALC!

 

[Fluff]

In that case you have no cells that are <20. Check that your numbers are real numbers & not text.

 

[meppwc]

Same result, and values exist less that 20 all the way down to 0

 

[Fluff]

That suggests that your "numbers" are in-fact text.

 

[Fluff]

Try this mod to Phuoc's suggestion

=FILTER(L2:O10000,MMULT((--L2:O10000<20)*(L2:O10000<>""),{1;1;1;1}),"")

 

[meppwc]

#VALUE

 

[Fluff]

In that case can you post some sample data using the XL2BB add-in