合并单元格的统计问题

向建超

如图，按照合并单元格来统计项目数量，但是否计入则按照该项目是否有批次为准，同一项目下多批次仅计一次。请教如何C17：D18的公式将如何编写？

工作簿11.rar (9.66 KB, 下载次数: 133)

2021-12-13 09:24 上传

点击文件名下载附件
工作簿11.rar

虎飞

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

用户浪得虚名

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