Question

excel中如何统计某数据区域内相同数据的行数？如下图，

要求在F1单元格中返回这个数据区域内出现的包含04 08 10的行数，返回值为4

Answer 1

如果要查找的值的数量不多的话可以这样做。如果要查找的值数量很多，貌似还是得想其他办法。

F1=sum(E1:E5)

Answer 2

f1公式:
=count(find("04,",a1:a10&","&b1:b10&","&c1:c10&","&d1:d10&","&e1:e10&",")*find("08,",a1:a10&","&b1:b10&","&c1:c10&","&d1:d10&","&e1:e10&",")*find("10,",a1:a10&","&b1:b10&","&c1:c10&","&d1:d10&","&e1:e10&","))
数组公式,按ctrl+shift+enter三键结束

Answer 3

=SUM(IF(ISERROR(SEARCH("04",A1:E10)),IF(ISERROR(SEARCH("08",A1:E10)),IF(ISERROR(SEARCH(10,A1:E10)),0,1))))
同时按CTRL SHIFT 回车键

Answer 4

在F1单元格输入公式：=IF(AND(COUNTIF(A1:E1,8)>0,COUNTIF(A1:E1,4)>0,COUNTIF(A1:E1,10)>0),4,"")

Answer 5

这个用VBA遍历吧没好办法！