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.

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.

• google-sheets

• This seems like it might be a viable solution: stackoverflow/questions/40515469/… – DBS Commented Jan 18 at 11:19
• no matter what i try, i cannot get my newlines to behave. – calclatingchocolte Commented Jan 18 at 12:04
• @DBS thanks, it might be. i'm not too familiar with QUERY or REGEX. EDIT: did not expect the enter key to send comment. i have found this REGEX question. as it seems QUERY takes REGEX i should be able to adapt to my needs. i created a sheet with sample data, and can't get any query to give any output. i tried the following strings: ^(?:[^e]*e){2}[^e]*$ ^(?:e){2}$ ^(?:[e]*e){2}$ ^(?:[e]){2}$ my sample data does contain a variety of quantities of the character e – calclatingchocolte Commented Jan 18 at 12:05
• Welcome to Stack Overflow. Please edit your question and insert a table of sample data together with another table that shows your manually entered desired results, making sure you're not describing an XY problem. Also consider sharing a publicly editable sample spreadsheet. There is a blank sheet maker that lets you share safely. – doubleunary Commented Jan 18 at 12:06

2 Answers 2

Assuming the column is A2:A, you can use:

=SUMPRODUCT(2=LEN(REGEXREPLACE(A2:A,"[^,]",)))

For each value in the range, the formula uses REGEXREPLACE to remove every character that's not a comma:

REGEXREPLACE(A2:A,"[^,]",)

Then it counts how many of those values have a length of exactly 2 using SUMPRODUCT and LEN.

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

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