Excel FILTER to return array non zero columns-CodePudding

I have an Excel table that has the following (plus more) data in it:

What I would like to do is filter the data to return all but the columns that have all 0 values in them, i.e. the return would be:

I can't for the life of me get my filter expression to return what I want, any help would be greatly appreciated!

CodePudding user response：

With BYCOL and LAMBDA:

=FILTER(B1:I4,BYCOL(B2:I4,LAMBDA(a,SUM(a)))>0)

Or as @BigBen pointed out:

=FILTER(B1:I4,BYCOL(B2:I4,LAMBDA(a,OR(a)))

Since BYCOL and LAMBDA are not available to all office 365 users yet here is one that does not use it.

=FILTER(B1:I4,TRANSPOSE(MMULT(--((TRANSPOSE(B2:I4)<>0)),SEQUENCE(ROWS(B2:I4),,1,0))>0))