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.
Share Improve this question asked Jan 18 at 11:11 calclatingchocoltecalclatingchocolte 12 bronze badges 4 |2 Answers
Reset to default 1Assuming 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().
^(?:[^e]*e){2}[^e]*$
^(?:e){2}$
^(?:[e]*e){2}$
^(?:[e]){2}$
my sample data does contain a variety of quantities of the charactere
– calclatingchocolte Commented Jan 18 at 12:05