3

I have a Sheet with the prices of some products Each column is a parameter.

How to use the FILTER() function in order to filter by multiple conditions

In this case I need to filter and see only the rows where the Power=I3 and the Manufacturer=I2, I am sure that I have to use the FILTER(FILTER() f

This is what I managed to create:

=FILTER(FILTER(A2:F,B2:B=I3),D2:D=I2)

but it returns the following error :

Error FILTER has mismatched range sizes. Expected row count: 7. column count: 1. Actual row count: 999, column count: 1.


UPDATE: I managed to do it with

=FILTER(A2:F,IF(ISBLANK(A2),1,A2:A=A2)*IF(ISBLANK(D2),1,D2:D=D2))

But it becomes too long and not elegant. I am sure that there is a better way to do it.

Especially if I want to create more conditions for each column.... enter image description here

2
  • 2
    @pjmg google-sheets-filter, according to it's tag excerpt and wiki, it is for questions about using the UI filter and filter view feature
    – Rubén
    Sep 27, 2019 at 13:49
  • Okay, I'll be more careful when I add tags to my edits. Thanks !
    – pjmg
    Oct 2, 2019 at 7:59

1 Answer 1

2

The "elegant" way to use FILTER to filter A2:F where B2:B (Power) =I3 and D2:D (Manufacturer) =I2 is

=FILTER(A2:F,B2:B=I4,D2:D=I3)

The above works because filter allow multiple criteria arguments.

Reference

1
  • Thank you for the answer. Unfortunately when some of the criteria is not set it still filters the results and returns N/A. Any idea how to fix it? Sep 28, 2019 at 14:39

Your Answer

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

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