Formula to count within multiple cells that container a specific letter?
- Thread starter Harry0
- Start date
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.
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.
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)
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)
shrivallabha
Excel Ninja
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
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.
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
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