How to count specific letters in range of cells?

G

Guest

I am looking for a formula to count the number of R's in a range.
=countif(a1:a3,"*R*")... is only giving me 2 - one for each cell there is an
"R"... not 5 for the total number of R's. I need a formula to give me that
"total" - 5 number. Any ideas????

A1 = BETH PAGEL (zero R's)
A2 = BARRY GUNTHER (3 R's)
A3 = RANDY ROST (2 R's)
 
R

Rick Rothstein \(MVP - VB\)

I am looking for a formula to count the number of R's in a range.
=countif(a1:a3,"*R*")... is only giving me 2 - one for each cell there is
an
"R"... not 5 for the total number of R's. I need a formula to give me
that
"total" - 5 number. Any ideas????

A1 = BETH PAGEL (zero R's)
A2 = BARRY GUNTHER (3 R's)
A3 = RANDY ROST (2 R's)
Click to expand...

Off the top of my head, this formula should work...

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

Rick
 
R

Rick Rothstein \(MVP - VB\)

I am looking for a formula to count the number of R's in a range.
Off the top of my head, this formula should work...

=SUMPRODUCT(LEN((A1:A3))-LEN(SUBSTITUTE((A1:A3),"R","")))
Click to expand...

By the way, the above is case-sensitive. If you need a case insensitive
formula, try this one...

=SUMPRODUCT(LEN((A1:A3))-LEN(SUBSTITUTE((A1:A3),{"r","R"},"")))

Rick
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Counting Specific Character(s) In A Range? 10
Addition function of multiple cells 10
Count If formula with week range 9
I still cant get the six most common numbers to come up 1
Averaging a row & dividing by only the qty. of cells with a value 3
count specific text that occurs in a range of cells 16
Sorting and COUNTIF 6
COUNTIF, Sorting, on Two Sheets 1

Top