FILTER range must be a single row or a single column. when trying to apply a condition across severa-CodePudding

Here is a regular filter function that works as expected:

=filter(
data!$A$2:$A,
date!$D$2:$D>=$A$16,
data!$D$2:$D<=$B$16,
data!$EC$2:$EC=$A23
)

This returns the subset of values in data!$A$2:$A that match the conditions. Great.

I would like to add a condition to a range of columns, that all that data!$CD$2:$CN are all equal to one. Tried:

=filter(
data!$A$2:$A,
date!$D$2:$D>=$A$16,
data!$D$2:$D<=$B$16,
data!$EC$2:$EC=$A23,
data!$CD$2:$CN=1
)

Then tried:

=filter(
data!$A$2:$A,
date!$D$2:$D>=$A$16,
data!$D$2:$D<=$B$16,
data!$EC$2:$EC=$A23,
sum(data!$CD$2:$CN)=11 # there's 11 columns in this range
)

I get the error above. I could manually add a condition for each of the 11 columns:

=filter(
data!$A$2:$A,
date!$D$2:$D>=$A$16,
data!$D$2:$D<=$B$16,
data!$EC$2:$EC=$A23,
data!$CD$2:$CD=1,
data!$CE$2:$CE=1,
... lost more in here all the way to ...
data!$CN$2:$CN=1
)

But that's going to get pretty repetitive.

Is there a way to filter across a range of columns, in this case I want to filter to only include rows where everything in data!$CD$2:$CN equals 1?

CodePudding user response：

You can use MMULT function to combine multiple columns into one criteria.

Ex:

In your case it would be:

=FILTER(data!$A$2:$A,MMULT(--(data!$CD$2:$CN=1),{1;1;1;1;1;1;1;1;1;1;1})=11)