CODEWITHSUNDEEP
excelexcel-formula
Rever92
Rever92

Reputation: 19

Excel Filter Formula based on another cell get any if empty

So I'm trying to get this sorted out for a while but can't seem to figure it out.

I am using the FILTER formula in order to filter a set of records in a table based on the value of a cell (actually multiple conditions, but nos really the point). The thing is, I would like the formula to filter the records if the condition cell has some text, but if it is empty, I would like it to return ALL records as if there was no filter, and I can't get it right...

This is the formula I am using:

=FILTER(Table;SI(D2<>"";Table[Streaming]=D2;Table[Streaming]=""))

Any clues?

Thanks

Upvotes: 0

Views: 1935

Answers (1)

teylyn
teylyn

Reputation: 35915

Nesting formulas is powerful, when the syntax of the formula is well understood. In the screenshot, this formula filters the table:

=FILTER(Table1,(IF(H2="",TRUE,Table1[Color]=H2))*(IF(H3="",TRUE,Table1[size]=H3))*(IF(H4="",TRUE,Table1[length]=H4)))

enter image description here

enter image description here

In case you want to duplicate the whole table if there are no filters specified, then wrap the above formula into an IfErrror() like this:

=IFERROR(FILTER(Table1,IF(H2="",TRUE,Table1[Color]=H2)*IF(H3="",TRUE,Table1[size]=H3)*IF(H4="",TRUE,Table1[length]=H4)*(COUNTA(H2:H4)>1)),Table1)

Upvotes: 1

Related Questions