合并单元格的统计问题

好地主

回复1楼,数组公式拉
C17 数组公式
=COUNT(0/(((FREQUENCY(IF(YEAR($F$2:$F$13)=B17,MATCH(LOOKUP(ROW($2:$13),ROW($2:$13)/($B$2:$B$13<>""),$B$2:$B$13)&$F$2:$F$13&$G$2:$G$13,LOOKUP(ROW($2:$13),ROW($2:$13)/($B$2:$B$13<>""),$B$2:$B$13)&$F$2:$F$13&$G$2:$G$13,)),ROW($1:$12))>=1))))

D17 数组公式
=TEXTJOIN("，",1,IF((FREQUENCY(IF(YEAR($F$2:$F$13)=B17,MATCH(LOOKUP(ROW($2:$13),ROW($2:$13)/($B$2:$B$13<>""),$B$2:$B$13)&$F$2:$F$13&$G$2:$G$13,LOOKUP(ROW($2:$13),ROW($2:$13)/($B$2:$B$13<>""),$B$2:$B$13)&$F$2:$F$13&$G$2:$G$13,)),ROW($1:$12))>=1),LOOKUP(ROW($2:$13),ROW($2:$13)/($B$2:$B$13<>""),$B$2:$B$13),""))

期待一片阳光

回复2楼>=600 ,数组公式拉
C17 数组公式
=COUNT(0/((FREQUENCY(IF((YEAR($F$2:$F$13)=B17)*(LOOKUP(ROW($2:$13),ROW($2:$13)/($E$2:$E$13<>""),$E$2:$E$13)>=600),MATCH(LOOKUP(ROW($2:$13),ROW($2:$13)/($B$2:$B$13<>""),$B$2:$B$13)&LOOKUP(ROW($2:$13),ROW($2:$13)/($E$2:$E$13<>""),$E$2:$E$13)&$F$2:$F$13&$G$2:$G$13,LOOKUP(ROW($2:$13),ROW($2:$13)/($B$2:$B$13<>""),$B$2:$B$13)&LOOKUP(ROW($2:$13),ROW($2:$13)/($E$2:$E$13<>""),$E$2:$E$13)&$F$2:$F$13&$G$2:$G$13,)),ROW($1:$12))>=1)))

D17 数组公式
=TEXTJOIN("，",1,IF((FREQUENCY(IF((YEAR($F$2:$F$13)=B17)*(LOOKUP(ROW($2:$13),ROW($2:$13)/($E$2:$E$13<>""),$E$2:$E$13)>=600),MATCH(LOOKUP(ROW($2:$13),ROW($2:$13)/($B$2:$B$13<>""),$B$2:$B$13)&LOOKUP(ROW($2:$13),ROW($2:$13)/($E$2:$E$13<>""),$E$2:$E$13)&$F$2:$F$13&$G$2:$G$13,LOOKUP(ROW($2:$13),ROW($2:$13)/($B$2:$B$13<>""),$B$2:$B$13)&LOOKUP(ROW($2:$13),ROW($2:$13)/($E$2:$E$13<>""),$E$2:$E$13)&$F$2:$F$13&$G$2:$G$13,)),ROW($1:$12))>=1),LOOKUP(ROW($2:$13),ROW($2:$13)/($B$2:$B$13<>""),$B$2:$B$13),""))

忠厚持家久

感谢！佩服佩服！感谢！我得好好消化一下，尤其 Frequency 函数的用法。我自己基本用 Sumproduct 和 IF 函数加加减减虽然也完成了C17，但可读性太差；D17还没折腾出来。

振兴地产

有一个小问题，比如，同一项目下有超过两批是同一年时，项目数会重复计数，注释内也重复项目名称（这里应该是不重复的）。

王亚雄

把有问题的都置放附件中,也把期望的答案置放其中

提刀夜行

=COUNT(MATCH(ROW($1:$12),MMULT(N(ROW($1:$12)>=COLUMN(A:L)),N($A$2:$A$13<>""))*(TEXT($F$2:$F$13&"","e")=B17&""),))

第二个缩不来，新版还能写写=TEXTJOIN("、",1,XLOOKUP(UNIQUE(MMULT(N(ROW($1:$12)>=COLUMN(A:L)),N($A$2:$A$13<>""))*(TEXT($F$2:$F$13&"","e")=B17&"")),A2:A13,B2:B13,""))

西瓜霜

效果非常好！感谢！
唯一小不足，增减行数时，公式中的Column（A:L）不会自动随之变化，需要手工修改。

虎虎医生

用笨方法搞定了。
Column(A:L)用 COLUMN(INDIRECT("C1:C"&ROW(A12),)替换，这样增加、删减行数时不用修改公式。