"ONLY FILTER IF" Function?

Question

I have a long list of sentences(Essay Sentences) and their attributes that I need to filter out and add together only specific ones. I am using the new Filter() function. I have four different filter options(Levels, AP Scores, Hellman Scores, and Highest Level) and I am trying to find a way to choose some filters and not all.

Right now I have this, which takes 'Essay Sentences'!H5:H9999 and has ALL the filters...

=SUM(FILTER(FILTER(FILTER(FILTER('Essay Sentences'!H5:H9999, 'Essay Sentences'!G:G=B2, 0), 'Essay Sentences'!C:C=C2, 0), 'Essay Sentences'!D:D=D2, 0), 'Essay Sentences'!E:E=E2, 0))

The problem I am having is how to ignore a specific filter if I don’t want it to apply in one instance.

For example, I might want to filter out all sentences that don't have a “Level” of 4100 (G4). In this example, I don’t want to filter out anything using the “AP Score” column, but in another example, I might want to.

Is there a way to have multiple filters, but still have the ability to ignore one?

Answer 1

Note: the question is about the Excel new Filter() function, which is part of the new Dynamic Array formulas that are currently available to some Office 365 Insider builds only.

Instead of nesting several FILTER() functions, you can multiply the different parameters

=FILTER('Essay Sentences'!H5:H9999, ('Essay Sentences'!G:G=B2)*('Essay Sentences'!C:C=C2)*('Essay Sentences'!D:D=D2)*('Essay Sentences'!E:E=E2))

This is basically a filter with AND between the conditions. (To get the OR result, use the + operator instead of the *.)

Since your comparison values are in cells, you can switch off the filter condition by putting a specific value into that cell, for example the text "all". Then you can use an IF condition inside the filter condition that returns a true when the cell contains "all".

See this at work in the following screenshot. The formula in cell H8 is

=FILTER(Sheet6!$B$3:$E$10,
(IF(I2="all",TRUE,Sheet6!$B$3:$B$10=I2))*
(IF(I3="all",TRUE,Sheet6!$C$3:$C$10=I3))*
(IF(I4="all",TRUE,Sheet6!$D$3:$D$10=I4))*
(IF(I5="all",TRUE,Sheet6!$E$3:$E$10=I5)))

and it returns the filtered table. You can filter on just the value column and sum that with the formula in cell K2, which is

=SUM(FILTER(Sheet6!$F$3:$F$10,
(IF(I2="all",TRUE,Sheet6!$B$3:$B$10=I2))*
(IF(I3="all",TRUE,Sheet6!$C$3:$C$10=I3))*
(IF(I4="all",TRUE,Sheet6!$D$3:$D$10=I4))*
(IF(I5="all",TRUE,Sheet6!$E$3:$E$10=I5))))