# Count repeated text

0

Hello!

What formula may help to get the number of b=5 with:

A1 b1

A2 bubble

A3 ball

Thank you!

### Discussion

Sorry, can't make sense of your question.
Variatus (rep: 2354) Oct 6, '17 at 7:11 am
@Don There is a bit of a problem here yet. Notice that my response was posted 7 hours before the question was asked.
Variatus (rep: 2354) Oct 6, '17 at 7:15 am
Refresh the page and it should be accurate. When you post in the discussion section, the data comes up without refreshing the page and without re-running the script that adjusts the posts time for your timezone. As such, you see the time for the server while all other times on the page should have already been adjusted for your timezone. That's basically how it works.
don (rep: 1521) Oct 6, '17 at 7:28 am
Glad to say! It does work. :-)
Variatus (rep: 2354) Oct 6, '17 at 7:38 am
Sorry if it is not clear.
There are 3 words (b1, bubble and ball) in A1:A3. =COUNTIFS(A1:A3,"*b*") results in "3" whereas I want "5". Hope I'm understood.
Chhabi Acharya (rep: 102) Oct 6, '17 at 8:57 am

1

Try =SUMPRODUCT(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,"b","")))

### Discussion

Thanks for the help. But I think it is case sensitive, right? So, I followed a clumsy way:
=SUMPRODUCT(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,"B","")))+SUMPRODUCT(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,"b","")))

Hope, you'll help.
Chhabi Acharya (rep: 102) Oct 8, '17 at 12:12 am
You can also try (adding the other case to formula): =SUMPRODUCT(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,"b","")))+SUMPRODUCT(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,"B","")))
queue (rep: 373) Oct 10, '17 at 12:28 pm