Would it be possible to use arrayformular for this condition? Sum all the rows that PID are the same, the result should be as in the image.
I tried this code, but I think it's too long, and if the PID exceed over 20 rows, it would not work.
=IF(A3<>A2,BJ3 IF(A3=A4,BJ4,0) IF(A3=A5,BJ5,0) IF(A3=A6,BJ6,0) IF(A3=A7,BJ7,0) IF(A3=A8,BJ8,0) IF(A3=A9,BJ9,0) IF(A3=A10,BJ10,0) IF(A3=A11,BJ11,0) IF(A3=A12,BJ12,0) IF(A3=A13,BJ13,0) IF(A3=A14,BJ14,0) IF(A3=A15,BJ15,0) IF(A3=A16,BJ16,0) IF(A3=A17,BJ17,0) IF(A3=A18,BJ18,0) IF(A3=A19,BJ19,0) IF(A3=A20,BJ20,0) IF(A3=A21,BJ21,0) IF(A3=A22,BJ22,0),0)
CodePudding user response：
With a table like this :
| ID | Value |
|---|---|
| 1 | 5 |
| 1 | 10 |
| 2 | 5 |
| 2 | 10 |
| 2 | 15 |
You have an expected output of :
| ID | Value | Sum |
|---|---|---|
| 1 | 5 | 15 |
| 1 | 10 | blank |
| 2 | 5 | 30 |
| 2 | 10 | blank |
| 2 | 15 | blank |
It is achievable with this formula (just drag it in your sum column) :
=IF(A2=A1,"",SUMIFS(B$2:B$12,A$2:A$12,A2))
It check if the ids are the same and then sum them, but only show them on the row where the id first appears
Found it on google by searching google sheets sum group by
CodePudding user response：
The following in C2 will generate the required answer without any copying-down required:
=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)),))
We are making a lookup table of grouped sums against the first row of each 'P#' group using QUERY, then using VLOOKUP to distribute the group sums to the first row in each group. Probably also doable using a SCAN/OFFSET combination as well, I think.