合并单元格的统计问题

娇艳梅花

D17 公式有问题？WPS 2019 不行，Excel 2021可以运行。但，合并单元格的首行不是所求年份时，结果会出错。

努力的方向

自解一下：
C17=COUNT(0/(MMULT(H2:H13*A2:A13,TRANSPOSE(ROW(2:13)^0))))
但如果增加附加条件，比如要求统计总数大于600的，那么公式该如何改呢？

然得

回复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）不会自动随之变化，需要手工修改。

口罩卡

自解一下：
C17=COUNT(0/(MMULT(H2:H13*A2:A13,TRANSPOSE(ROW(2:13)^0))))
但如果增加附加条件，比如要求统计总数大于600的，那么公式该如何改呢？