sum values of a column for regions where values in

问题:

I need to write an excel formula which will provide me with the column Sum given colums Cat(category) and Val(value). Based on the values in cat, values in val are separated in regions and sum is calculated for every region

Cat Val Sum
1   1   
1   2   
1   7   
1   8   18
2   9   
2   14  
2   15  
2   16  54
3   17  
3   19  
3   20  
3   21  77
1   22  
1   23  
1   24  
1   25  
1   26  120

回答1:

try,

=IF(A2<>A3, SUM(B2:INDEX(B:B, AGGREGATE(14, 6, ROW($2:2)/((A$2:A2=A2)*(A$1:A1<>A2)), 1))), TEXT(,))