最新消息:雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客

google sheets - How to count the amount of times a substring appears in a range of cells? - Stack Overflow

programmeradmin2浏览0评论

each cell contains either Top, Top, Top, Bottom or Bottom, Bottom

I'm currently using =COUNTIF(B3:E3,"*"&"Top"&"*") which only counts 1.

How would I make it so it counted the 2 separate "Top"'s in a cell?

each cell contains either Top, Top, Top, Bottom or Bottom, Bottom

I'm currently using =COUNTIF(B3:E3,"*"&"Top"&"*") which only counts 1.

How would I make it so it counted the 2 separate "Top"'s in a cell?

Share Improve this question asked Jan 29 at 20:24 Ryan LakerRyan Laker 274 bronze badges 4
  • Have you tried =COUNTIF(SPLIT(JOIN(",",B3:E3),","),"*top*")? – PatrickdC Commented Jan 29 at 20:31
  • @PatrickdC not working, is saying expected 2 arguments for COUNTIF but got 1 argument – Ryan Laker Commented Jan 29 at 20:39
  • How did you apply the formula? Can you provide sample data in markedown table format? – PatrickdC Commented Jan 29 at 20:41
  • or is your file's region settings in EU? Which uses ; instead of ,? – PatrickdC Commented Jan 29 at 20:42
Add a comment  | 

1 Answer 1

Reset to default 1

Add SPLIT and JOIN Functions

For a single row input, you may use:

=COUNTIF(SPLIT(join(",",B3:E3),","),"*top*")

For a multiple rows input, you may use:

=MAP(B3:B5,C3:C5,D3:D5,E3:E5,LAMBDA(b,c,d,e, COUNTIF(SPLIT(join(",",b,c,d,e),","),"*top*")))

Output (If counting "Top"):

Top, Top Top, Top Top, Top Top, Top 8
Top, Bottom Top, Top Top, Bottom Top, Top 6
Top, Top Bottom, Bottom Top, Top Bottom, Bottom 4

EU Equivalent Formulas:

Formula 1:

=COUNTIF(SPLIT(join(",";B3:E3);",");"*top*")

Formula 2:

=MAP(B3:B5;C3:C5;D3:D5;E3:E5;LAMBDA(b;c;d;e; COUNTIF(SPLIT(join(",";b;c;d;e);",");"*top*")))
发布评论

评论列表(0)

  1. 暂无评论