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?

• google-sheets

• 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

1 Answer 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"):

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*")))