dynamic range in arrayformula without using indirect?

1
votes

I have this formula in spreadsheet that works as supposed. The formula is placed in "O:O" (this one is from O10). (I've translated the formula from danish - hope I did it right)

=IFERROR(IF(I10<>"";sum(INDIRECT("L"&MATCH(LEFT(B10;LEN(B10)-6);B:B;0)):INDIRECT("L"&ROW()-1));"");"Fejl/Mangler reference")

Here's the result enter image description here

The data is imported from another sheet and the lines can change over time and there can be added/removed rows that should be taken into consideration in each Sub Total.

This is why I want to convert the formula to an arrayformula - or another solution?

What I thought would work was:

=Arrayformula(IF(ROW(R:R)=5;"Balance";IF(ROW(R:R)<7;"";IF(ROW(R:R)=7;"Sub Total";IF($I:$I<>"";sum(INDIRECT("L"&MATCH(LEFT(B:B;LEN(B:B)-6);B:B;0))&":"&INDIRECT("L"&ROW()-1));"")))))

I guess is the Indirect so I've tried also to do it with a vlookup but that I couldn't figure out either.

Any good ideas?

Here's a link for the document: Spreadsheet

1
google-sheetssumgoogle-sheets-formulaarray-formulascumulative-sum

1 Answers

2
votes

cell P8:

=ARRAYFORMULA(IF(J8:J="";; SUMIF(ROW(O8:O); "<="&ROW(O8:O); O8:O)))

cell O8:

=ARRAYFORMULA(IF(I8:I="";;
 SUMIF(ROW(B8:B); "<="&ROW(B8:B); L8:L)-SUMIF(ROW(B8:B); "<"&VLOOKUP(
 VLOOKUP(ROW(B8:B); IF(B8:B<>""; {ROW(B8:B)\ SUBSTITUTE(B8:B; " i alt"; )}); 2; 1); {
 VLOOKUP(ROW(B8:B); IF(B8:B<>""; {ROW(B8:B)\ SUBSTITUTE(B8:B; " i alt"; )}); 2; 1)\
 ROW(B8:B)}; 2; 0); L8:L)))

0