google-sheets

count number of cells with precise number of repeated characters


in google sheets, i am looking for a way to run something like COUNTIF or ARRAYFORMULA - whatever works - that counts the number of cells which themselves contain an explicitly defined number of repeated characters.

for example, a column of data contains a series of comma-separated lists. i want to count how many cells in that column contain exactly two commas. i don't want to count the number of commas in the column, nor do i want to count the number of cells that contain any quantity of commas.

i want to count the number of cells that contain exactly two commas, and no other quantity of commas.

while this is a practical example - it is what i am currently trying to do - i am looking for a generic solution that can be used to count any number of any character.


Solution

  • One way to do that is to use the len() - len(substitute()) pattern, like this:

    =let( 
      numChars, map(tocol(A2:A, 1), lambda(s, 
        len(s) - len(substitute(s, D2, "")) 
      )), 
      counta(ifna(filter( 
        numChars, 
        numChars = D3 
      )))  
    )
    

    screenshot

    See substitute(), let(), map(), lambda(), tocol() and filter().