Special count (Alt + Enter)

[hesam bahrani]

Hi,

We want to count down a series of duplicate words, now you are in a cell with a single word of all and with ALT + enter separating
How do we count them?

For Example in this column count (A1:A5 ) of "Hesam" is 6


[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Hesam[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Ronaldo
Hesam [/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Hesam
Hesam[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Hesam
Ronaldo [/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Hesam[/TD]
[/TR]
</tbody>[/TABLE]

Thank you!

 

[Norie]

Probably a better way but you could try this array formula.

=SUM(LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,"Hesam","")))/LEN("Hesam")

In fact if your version of Excel has the TEXTJOIN function you could use this non-array formula.

=(LEN(TEXTJOIN("",FALSE,A1:A5))-LEN(SUBSTITUTE(TEXTJOIN("",FALSE,A1:A5), "Hesam", "")))/LEN("Hesam")

 

[steve the fish]

You could use sumproduct. Still array but doesnt need the CSE:

=SUMPRODUCT(LEN(A1:A5)-LEN(SUBSTITUTE(PROPER(A1:A5),"Hesam","")))/LEN("Hesam")

 

[hesam bahrani]

Probably a better way but you could try this array formula.

=SUM(LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,"Hesam","")))/LEN("Hesam")

In fact if your version of Excel has the TEXTJOIN function you could use this non-array formula.

=(LEN(TEXTJOIN("",FALSE,A1:A5))-LEN(SUBSTITUTE(TEXTJOIN("",FALSE,A1:A5), "Hesam", "")))/LEN("Hesam")

Thank you!!

 

[hesam bahrani]

You could use sumproduct. Still array but doesnt need the CSE:

=SUMPRODUCT(LEN(A1:A5)-LEN(SUBSTITUTE(PROPER(A1:A5),"Hesam","")))/LEN("Hesam")

very good!!