# Formula to count within multiple cells that container a specific letter?

#### Harry0

##### Member
if the cells says
sosos
psspp
xxxx
(and another 100 cells)

Is their one formula to give you the number of how many "s" or any other character to reference another cell all the cells have, which is 5?
Thanks

#### Peter Bartholomew

##### Well-Known Member
There is a trick for this; namely substituting a null string for every "s" you find.
If your initial range of 100 or so cells is named 'data', then the named formula 'remainder'
= SUBSTITUTE( data, "s", "" )

defines the array of unwanted characters. The result you require is then given by
= SUM( LEN(data) - LEN(remainder) )

At the risk of causing massive confusion, Microsoft has just released a beta test function LET, which allows this to be written
= LET(
remainder, SUBSTITUTE(data,"s",""),
instances, LEN(data) - LEN(remainder),
SUM(instances)
)

The LET function allows the user to define names for use within a single formula.

#### Harry0

##### Member
not working well or I am just confused.
Sorry I have excel 2007 (I like the quick options at the top than the tabs)
I will try to combine cells and find it all within 1 cell for the formula to be
=LEN(a1)-LEN(SUBSTITUTE(a1,"s",""))

Thank anyway

#### Peter Bartholomew

##### Well-Known Member
I have named the three cells containing text 'data'
Then I used Name Manager to define a named formula 'instances'
= LEN(data) - LEN( SUBSTITUTE( data, "s", "" ) )
The result is then given by
= SUM(instances)

#### Attachments

• 9.1 KB Views: 3

#### shrivallabha

##### Excel Ninja
if the cells says
sosos
psspp
xxxx
(and another 100 cells)

Is their one formula to give you the number of how many "s" or any other character to reference another cell all the cells have, which is 5?
Thanks
You can try SUMPRODUCT like below.
=SUMPRODUCT(LEN(A2:A101)-LEN(SUBSTITUTE(A2:A101,"s","")))

#### Peter Bartholomew

##### Well-Known Member
@shrivallabha

That would do it!

I haven't used a direct cell reference for about 8 years now and, since I have been using Office 365, the SUMPRODUCT approach is largely forgotten. Even before that, rather than using SUMPRODUCT as an array wrapper (I think Google sheets has an ARRAY function?), I tended to build the array formulas within a sequence of defined names. It worked well for me but doesn't exactly help answer questions on this forum.

Perhaps I should get over my distaste for the practice of direct cell referencing but, in reality, I am more likely to carry on with formulas like
= SUM( X*Y )
that I am to consider reverting to traditional methods.

Anyway, thank you for your help.
Peter

#### shrivallabha

##### Excel Ninja
@Peter Bartholomew

I do not see it from any particular perspective. It was OP's response indicating he was using Excel 2007 which got me to post this formula. As an aside, I am sure this request has come before and a search on google or this forum may provide a direct answer to it.

I believe with the advent of Power BI and Google Sheets (which does have an ARRAY function) as well as MS adding features by day, the scene is going to be dynamic and each user will tend to have an evolving style which may or may not conform to our style or liking! I hope Harry0 will not mind us digressing from the topic.

#### Harry0

##### Member
Thanks
Peter "Name Manager" i dont know that trick. So I had to go with shrivallabha which worked.
I dont mind the conversation.

Which the name manager would explain why nothing was working for my last post. I will probably use substitute 255 times which would take more power but would do the job.

Thanks