Would it be possible to array the following conditions?

I previosly asked for the formula to sum if the value in column A are the same, which I got it but I still need to drag it down on every rows.

=IF(A2=A1,"",SUMIFS(B$2:B$12,A$2:A$12,A2))

Which the results will appear as I wanted:

ID  Value   Sum
1   5   15
1   10  blank
2   5   30
2   10  blank
2   15  blank
3   10  35
3   10  blank
3   15  blank

I also, got the solution where for the array:

=arrayformula(if(len(A2:A),ifna(vlookup(row(A2:A),query({row(A2:B),A2:B},"select min(Col1),sum(Col3) where Col2 is not null group by Col2"),2,false)),))

But it only seems to work if I only have 2 columns of data (A and B), but my data is far apart. I tried to adjust the formula, but it doesn't seems to work correctly.

CodePudding user response：

As written in

then to output it per row:

=INDEX(IFNA(VLOOKUP(A2:A, 
 QUERY({A2:A, BI2:BI}, "select Col1,sum(Col2) group by Col1"), 2, )))

to output it only per the first occurrence of category:

=INDEX(IFNA(VLOOKUP(IF(COUNTIFS(A2:A, A2:A, ROW(A2:A), "<="&ROW(A2:A))=1, A2:A, ), 
 QUERY({A2:A, BI2:BI}, "select Col1,sum(Col2) group by Col1"), 2, )))

which can be reshaped to:

=INDEX(LAMBDA(x, IFNA(VLOOKUP(IF(COUNTIFS(x, x, ROW(x), "<="&ROW(x))=1, x, ), 
 QUERY({x, BI2:BI}, "select Col1,sum(Col2) group by Col1"), 2, )))(A2:A))