Sheets Arrayformula: increment range size row by row

Question

I am doing a tiny home store overview lists. At the movement sheet I want to sum ONLY the same and previous rows but never mix the results with the future row values.

As I use mostly ARRAYFORMULAs I want to do this also with it. But I cannot find a way to increment the sumif range row by row.

By a drag-down formula it is very easy but in that case the formel will not be extended automatically to all new lines, and it makes the entire workaround just unnecessary complicated. :(

So to being very simple: How can I achieve with an ARRAYFORMULA that also the sumif range is increasing and not always the entire columns are counted?

By a drag-downed references it works well but how to achieve this by Arrayformula:

=SUMIF(A$3:A3,A3,B$3:B3)
=SUMIF(A$3:A4,A4,B$3:B4)
=SUMIF(A$3:A5,A5,B$3:B5)...

This is considering always the entire columns:

=ArrayFormula(SUMIF(A$3:A,A3:A,B3:B))

Any hint is welcome how to change the range row by row and achieve the first pattern with ARRAYFORMULA. Unfortunately I haven't found a solution here: ARRAYFORMULA Auto-Increment

Example link Green is the target and red actually I could made with Arrayformula.

z.. · Accepted Answer · 2025-09-21 14:27:17Z

3

One way to do this is by using the MAP function

=MAP(A3:A, B3:B, LAMBDA(α, β, SUMIF(A3:α, α, B3:β)))

To display a blank cell when either column is blank, you can use

=MAP(A3:A, B3:B, LAMBDA(α, β, 
   IF(OR(α = "", β = ""), , SUMIF(A3:α, α, B3:β))
 ))

edited Sep 21 at 14:27

answered Sep 21 at 14:22

z..

14k2 gold badges10 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Levente_Agi Szilagyi_Dano Sep 23 at 7:54

Can we state that Arrayformula cannot manage range size? Range is always the entire column or the given range, but it is constant for all the rows in all functions inside the arrayformula.

z.. Sep 23 at 11:34

the range inside the lambda grows dynamically depending on the row we are in. The criteria range, for example, In row 1 is A3:A3, in row 2 it's A3:A4, and so on... The same applies to the sum range as well.

bricks96 · Accepted Answer · 2025-10-19 13:05:05Z

Here's a clever way to get all the running totals based on one or more conditions. If your values to sum are in A2:A21, and you various value/checkboxes/dropdowns in columns B2:D21, then the following formula will display the running total for each of the different combinations of those three columns.

=INDEX(LET(a,B2:B21&C2:C21&D2:D21&REPT("♦",24-ROW(B2:B21)),SUMIF(a,a&"*",A2:A21)))

It works because at each row, using the wildcard means only the similar combined values with more special characters will be included. None below the current row.

Value  Combined
  4    X123B♦♦♦♦
  3    Y123C♦♦♦
  7    X123B♦♦
  1    X123B♦

SUMIF(Combined,Combined&"*",Value)

I use 24 in my formula for determining how many special characters to begin with. Just make sure that number is at least the number of rows in your data.

Collectives™ on Stack Overflow

Sheets Arrayformula: increment range size row by row

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related