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