Create single formula for several different lines (array) (Google Sheets)

Question

In the AD column I have this sequence of values:

2
3
4

These values refer to rows in a column on another page.

In each line in AE column I use this formula:

=IF(AD1="","",IFERROR(SUM(FILTER(INDIRECT("'Registro Geral'!O2:O"&AD1)/100,REGEXMATCH(INDIRECT("'Registro Geral'!H2:H"&AD1),SUBSTITUTE(SUBSTITUTE(JOIN("|",$V$1:$V$4),"||",""),"|||",""))=TRUE))))

=IF(AD2="","",IFERROR(SUM(FILTER(INDIRECT("'Registro Geral'!O2:O"&AD2)/100,REGEXMATCH(INDIRECT("'Registro Geral'!H2:H"&AD2),SUBSTITUTE(SUBSTITUTE(JOIN("|",$V$1:$V$4),"||",""),"|||",""))=TRUE))))

=IF(AD3="","",IFERROR(SUM(FILTER(INDIRECT("'Registro Geral'!O2:O"&AD3)/100,REGEXMATCH(INDIRECT("'Registro Geral'!H2:H"&AD3),SUBSTITUTE(SUBSTITUTE(JOIN("|",$V$1:$V$4),"||",""),"|||",""))=TRUE))))

In short, this formula is getting a running Sum of values in the other sheet based on whether or not the corresponding cell in another column of the same sheet appears in a set of values.

When I try to add ARRAYFORMULA so that I don't have to have a formula on each line, leaving only in AE1, the values that return on all lines are exactly the same value.

Test Formula Fail:

=ARRAYFORMULA(IF(AD1:AD="","",IFERROR(SUM(FILTER(INDIRECT("'Registro Geral'!O2:O"&AD1:AD)/100,REGEXMATCH(INDIRECT("'Registro Geral'!H2:H"&AD1:AD),SUBSTITUTE(SUBSTITUTE(JOIN("|",$V$1:$V$4),"||",""),"|||",""))=TRUE)))))

Link to Spreadhseet example:
https://docs.google.com/spreadsheets/d/1qIv6KnLv-EwJQXRrk7ucuqY-XuJhkIHOCtih9FpAg6U/edit?usp=sharing

This looks like a pretty classic example of an XY.... xyproblem.info — MattKing

Calculuswhiz Calculuswhiz · Accepted Answer · 2020-10-01T13:25:38

You're trying to do a running summation on O based on whether the corresponding value in the H column appears in the Filtered values.

We can do this with a matrix multiplication using a lower-triangular matrix and the listed values, selecting which ones to zero out based on certain conditions using IF.

=ArrayFormula(MMULT(
    N(SEQUENCE(D2)>=SEQUENCE(1,D2)),
    ARRAY_CONSTRAIN(
        IF(
            ('Registro Geral'!O2:O<>"")*
                IFNA(MATCH('Registro Geral'!H2:H,V:V,0)),
            'Registro Geral'!O2:O
        )/100,
        D2,
        1
    )
))

Why this works

The lower-triangular matrix looks like

1 0 0 0 0 ... up to N columns
1 1 0 0 0
1 1 1 0 0
1 1 1 1 0
1 1 1 1 1
... up to N rows

The Column you want to sum looks like

Value 1
Value 2
...
Value N

So when you multiply the two, you get a new matrix of dimension N x 1:

Value 1
Value 1 + Value 2
...
Value 1 + ... + Value N

If we don't want to sum a value, then we can zero it out with a conditional so that it never gets added.

Create single formula for several different lines (array) (Google Sheets)

1 Answers

Why this works