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 |1 Answer
Reset to default 1Add 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*")))
=COUNTIF(SPLIT(JOIN(",",B3:E3),","),"*top*")
? – PatrickdC Commented Jan 29 at 20:31;
instead of,
? – PatrickdC Commented Jan 29 at 20:42