I would like to use sumproduct (open to any other method as well) in excel to filter array values from a data table. Instead of just filtering a column for a single value, I want to filter column D for whether each row value "is in" the values in column A. Here is a sample of my data:

I am using the following formula to filter for C = 127281694, D = Algebra II, E = YR, and F < 100: =SUMPRODUCT(($C$2:$C$10=127281694)*($D$2:$D$10=$A$2)*($E$2:$E$10="YR")*($F$2:$F$10<100)*1). The formula correctly returns the value of 1, as there is one row that meets these conditions.

Instead of filtering for just D = Algebra II, I want to filter if D "is in" a list of values, such as {"Algebra II", "Physics", "AP US History", "Korean III"}, so that the array will return all rows where column D = Algebra II OR Physics OR AP US History OR Korean III. Does anyone know a solution for this problem?

CodePudding user response：

You could add another column to check if the respective single row value of column D XMATCHes with any row of column A. Then use this new column as another criteria in your formula.

If it works out, you can integrate it directly into your formula w/o having the additional column.

CodePudding user response：

This probably has to be entered as an

(I chose an alternative value for C so that there would be more than 1 result)

CodePudding user response：

If you want to filer all rows that D column contain values from column A then use below formula-

=FILTER(B1:E9,COUNTIFS(A1:A4,C1:C9))

From your given formula in post. You can use below formula to return rows as per multiple criteria (Yellow highlighted).

=FILTER(B1:E9,COUNTIFS(A1:A4,C1:C9)*(B1:B9=127281694)*(D1:D9="YR")*(E1:E9<100))