Custom formula that performs the sum sequentially on a spilled range

user_11721358 0
Viewed 36

Good afternoon everyone, I have created a custom formula with LAMBDA function that performs a sum of values ​​sequentially, returning the summed values ​​in a spilled range.

=LAMBDA(range;s; SUMIFS(range; s;"<="&s ))(C11:C15;E11:E15)

enter image description here

My problem is that to perform the sum as shown in the image, I have to select two ranges: range of values ​​to add and the range of the sequence. And I need help because the SUMIFS function does not allow me to put the SEQUENCE function in the range_criteria parameter. I would like my LAMBDA function, just by selecting the range of values ​​to add, to do the same result as the image. I look forward to your help and suggestions.

NOTE: I've tried doing this and I think it should work, but I'm getting a warning message that there is a problem with the formula.

=SUMIFS(C11:C15; SEQUENCE(ROWS(C11:C15)); "<="& SEQUENCE(ROWS(C11:C15)))
excel-formula
1 Answers

You could do it with this formula:

=MMULT(TRANSPOSE((C11:C15)*(ROW(C11:C15)<=TRANSPOSE(ROW(C11:C15)))),SEQUENCE(ROWS(C11:C15),1,1,0))

In principle, this solution follows the same logic as your SUMIF approach.

user_171587030
Related