求个分组求和公式

evluan01

求个分组求和公式

jivy1212

1. =SUM(SUBTOTAL(4,OFFSET($C$1,ROW($1:$10),,2))*($A$3:$A$12=$A$2:$A$11)*($A$3:$A$12=F6))

复制代码

下拉

丢丢表格

=SUM(SUBTOTAL(4,OFFSET(C$1,ROW($1:$11),,2))*(A$2:A$12&A$3:A$13=F6&F6))

mykitami

=LET(a,FILTER($C$2:$C$12,$A$2:$A$12=F6),SUM(DROP(REDUCE("",SEQUENCE(ROWS(a)-1),LAMBDA(x,y,VSTACK(x,MAX(TAKE(DROP(VSTACK("",a),y),2))))),1)))

丢丢表格

jivy1212 发表于 2024-3-7 08:44
下拉

直接比较也行

=SUM(IF(C$2:C$12>C$3:C$13,C$2:C$12,C$3:C$13)*(A$2:A$12&A$3:A$13=F6&F6))

mykitami

=LET(a,FILTER($C$2:$C$12,$A$2:$A$12=F6),SUM(DROP(REDUCE("",SEQUENCE(ROWS(a)-1),LAMBDA(x,y,VSTACK(x,MAX(TAKE(DROP(VSTACK("",a),y),2))))),1)))

wlc5201130

=SUM(IF($C2:$C11>$C3:$C12,$C2:$C11,$C3:$C12)*($A2:$A11=F6)*($A2:$A11=$A3:$A12))

evluan01

丢丢表格 发表于 2024-3-7 09:02
=SUM(SUBTOTAL(4,OFFSET(C$1,ROW($1:$11),,2))*(A$2:A$12&A$3:A$13=F6&F6))

大佬那个组内容怎么用公式输出来

我的城市你来过

=TEXTJOIN("，",,IF(LENB(TRIM(MID(TEXTJOIN(REPT(" ",66),,IF($A$2:$A$12=$F6,B$2:B$12,"")),ROW($A$1:$A$10)*66-65,132)))>2,TRIM(MID(TEXTJOIN(REPT(" ",66),,IF($A$2:$A$12=$F6,B$2:B$12,"")),ROW($A$1:$A$10)*66-65,132)),""))

=SUM(IF(CHOOSEROWS(TOCOL(ISNUMBER(IF($A$2:$A$12=$F6,$C$2:$C$12,""))*IF($A$2:$A$12=$F6,$C$2:$C$12,""),3),ROW(INDIRECT("$A$1:$A"&COUNT(IF($A$2:$A$12=$F6,$C$2:$C$12,""))-1)))>CHOOSEROWS(TOCOL(ISNUMBER(IF($A$2:$A$12=$F6,$C$2:$C$12,""))*IF($A$2:$A$12=$F6,$C$2:$C$12,""),3),ROW(INDIRECT("$A$2:$A"&COUNT(IF($A$2:$A$12=$F6,$C$2:$C$12,""))))),CHOOSEROWS(TOCOL(ISNUMBER(IF($A$2:$A$12=$F6,$C$2:$C$12,""))*IF($A$2:$A$12=$F6,$C$2:$C$12,""),3),ROW(INDIRECT("$A$1:$A"&COUNT(IF($A$2:$A$12=$F6,$C$2:$C$12,""))-1))),CHOOSEROWS(TOCOL(ISNUMBER(IF($A$2:$A$12=$F6,$C$2:$C$12,""))*IF($A$2:$A$12=$F6,$C$2:$C$12,""),3),ROW(INDIRECT("$A$2:$A"&COUNT(IF($A$2:$A$12=$F6,$C$2:$C$12,"")))))))

飞天篮球猪

供参考...