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.)
4
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)
2
FILTER(array,SUM(CHOOSECOLS(array,countedColumns)>0))
The FILTER function requires the second argument to be an array of the same height as the first argument but SUM produces a sing value only.
Guessing you want to compare the sum of column1 and column2 with a given number in each row, your formula should look like:
FILTER(array,CHOOSECOLS(array,30)+CHOOSECOLS(array,33)>0)
1