Search code examples
google-sheets

How to count the amount of times a substring appears in a range of cells?

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?

Solution

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