Search code examples
excelexcel-formula

Filtering an Array (not Range) based on a SUM of CHOOSECOLS applied to it

I have a series of cells with variations of a long formula which involves filters.

It takes a certain array, does some counting to some of its columns (checking the number of nonempty cells, then the number of positive cells), but then I need to filter that array further based on the sum of certain of its columns. The important nuance is that I need this formula to be pretty uniform, only changing the first two variables (label and countedColumns) for ease of mass implementation. This seemed to work great until I had to reference the proper cells inside the row for the sake of filtering, and to sum the values of those cells.

So far I tried something roughly like this (failing at the filtering stage):

=LET(
label, " SomeLabel ",
countedColumns, {30,33},

array, IF(ArrayOfTheDay!$A$3:$ZZ$999="","",ArrayOfTheDay!$A$3:$ZZ$999),

countedCells, CHOOSECOLS(array, countedColumns),
countedCellsClean, IF(countedCells="","",countedCells),
attemptCount, SUMPRODUCT(--(countedCellsClean<>"")),
successCount, SUM(countedCellsClean),

filteredSuccessData, FILTER(array, SUM(CHOOSECOLS(array,countedColumns)>0)), // this is not working
listOfSuccessDataAsText, // this will TextJoin/Concatenate certain cells from List

successCountText, IF(successCount>0, successCount&": ", "-"),
attemptCountText, IF(attemptsCount>0, " attempts: "&attemptCount, ""),
CONCATENATE(label, successCountText, ": ", listOfSuccessDataAsText," separator ", attemptCountText)
)

How can I properly choose the right columns within a row for the sake of filtering rows based on their sums, or how else can I approach it so as to not need to specify the columns multiple times per such formula? (I.e. I would rather not have to manually write a bunch of (column1address>0)+(column2address>0)+() entries in each and every instance of this filter, and it would be best not to use addresses at all.)

Solution

  • The second argument of FILTER expects an array with the same number of rows as the first argument. However, SUM returns a single scalar value, not an array.

    If you want to turn multiple columns into a single column containing their sum, then use the MMULT function

    summedColumn, MMULT(countedCells,--TRANSPOSE(INDEX(countedCells,1,)=INDEX(countedCells,1,))), 

filteredSuccessData, FILTER(array, summedColumn>0)

    or the ByRow function:

    summedColumn, ByRow(countedCells, LAMBDA(rw, SUM(rw))), 

filteredSuccessData, FILTER(array, summedColumn>0)