Would it be possible to array the following conditions?
| Column A | Column BI | Column BK |
|---|---|---|
| 1 | 10 | 20 |
| 1 | 10 | |
| 2 | 100 | 20 |
| 2 | -30 | |
| 2 | -50 |
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.
As written in How to use arrayformula with formulas that do not seem to support arrayformulas?,
Since SUMIFS doesn't fully support Arrays, use BYROW with OFFSET to refer to previous row:
=BYROW(A2:A6,LAMBDA(row,IF(row=OFFSET(row,-1,0),,SUMIFS(BI2:BI6,A2:A6,row))))