• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

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","")))
adjust the range to suit.
 

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
 
Top