How to array SUM by category

Question

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.

It looks like youv included code copied from somewhere else. If you're using someone else's work without giving them credit, that constitutes plagiarism, which is not welcome on Stack Exchange. To fix it, [edit] to make sure you do **ALL** the following: 1.Include a [link](/editing-help#links) to the source, 2. Mention the author's name, and 3. [Quote](/editing-help#simple-blockquotes) the copied content. For more details, see [referencing help](/help/referencing) and [this FAQ](https://meta.stackexchange.com/q/160077/343832). — TheMaster, Nov 01 '22 at 08:11

score 0 · Accepted Answer · answered Nov 01 '22 at 09:38

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))))

score 0 · Answer 2 · answered Nov 01 '22 at 10:53

there is no need to reinvent the wheel. try:

=QUERY({A:A, BI:BI}, "select Col1,sum(Col2) group by Col1")

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))

How to array SUM by category

2 Answers2