Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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!

Answer
Discuss

Discussion

Sorry, can't make sense of your question.
Variatus (rep: 4889) 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: 4889) 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: 1989) Oct 6, '17 at 7:28 am
Glad to say! It does work. :-)
Variatus (rep: 4889) 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: 111) Oct 6, '17 at 8:57 am
Add to Discussion

Answers

1
Selected Answer

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

Discuss

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: 111) 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: 467) Oct 10, '17 at 12:28 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login