Question

excel 怎么统计多个单元格中某字符的出现次数?

excel 怎么统计多个单元格中某字符的出现次数? 用 =COUNTIF(B36:B54,"#") 可以统计单个的 但里面字符再多就统计不了了
求大大们解答 先谢谢啦

Answer 1

一个单元格统计一次
=SUM(IF(LEN(B36:B54)-LEN(SUBSTITUTE(B36:B54,"#",""))>=1,1,0)) ctrl+shift+enter三键结束
一个单元格出现多次全统计
=SUMPRODUCT(LEN(B36:B54)-LEN(SUBSTITUTE(B36:B54,"#",)))

Answer 2

同一个单元格内有多个#时也要分别计数的话，可用公式：
=SUMPRODUCT(LEN(B36:B54)-LEN(SUBSTITUTE(B36:B54,"#",)))

Answer 3

=COUNTIF(B36:B54,"*#*")

Answer 4

=SUMPRODUCT(LEN(B36:B54)-LEN(SUBSTITUTE(B36:B54,"#",)))

Answer 5

完全不会，另请高明