Forum Discussion
How to add another criteria to FILTER function
Hi all, I am using the FILTER formula in my spreadsheet and i need to have two criteria "J" and "jj". How do I modify this ? !B3:B241="J"
Tinny426 Put each criterion between brackets and join them together with + for an OR, or * for an AND.
If you want to check if B3:B241 contains either "j" or "jj" it could look like this:
=FILTER( .........., (B3:B241="j")+(B3:B241="jj") )
8 Replies
Similar to what I suggested earlier, that would then be:
=SORT(FILTER('******************* SEP 11-27'!A3:C239,('****************** SEP 11-27'!B3:B239="j")+('****************** SEP 11-27'!B3:B239="jj")),3,-1)
Obviously, when there are no rows that match these criteria (either j or jj), then SORT will error out, and you need to wrap the formula in IFERROR. Something like this:
=IFERROR(SORT(FILTER('******************* SEP 11-27'!A3:C239,('****************** SEP 11-27'!B3:B239="j")+('****************** SEP 11-27'!B3:B239="jj")),3,-1), "No match")
Thanks for your help Riny, all working now.
Richard.
Hi, thanks for your suggestion, but it didn't seem to work.
Here is the full formula, which may help.
=SORT(FILTER('******************* SEP 11-27'!A3:C239,'****************** SEP 11-27'!B3:B239="j"),3,-1)
I need the results to include rows with both J and JJ in column B3:B239
Thanks Rintinman.
Another method, especially if you have more than two criteria to meet:
=SORT(FILTER('******************* SEP 11-27'!A3:C239, ISNUMBER(MATCH('******************* SEP 11-27'!B3:B239, {"j";"jj"}, 0)), "NA"),3,-1)you can set up the matrix {"j";"jj"} with all strings you need.
Tinny426 Put each criterion between brackets and join them together with + for an OR, or * for an AND.
If you want to check if B3:B241 contains either "j" or "jj" it could look like this:
=FILTER( .........., (B3:B241="j")+(B3:B241="jj") )
