How to use LEN, SUBSTITUTE functions?

[IMA_Saihat]

Hi,
To find the number of times that a certain text appears within a specified range use the following formula:
=SUM(LEN($B$2:$B$11)-LEN(SUBSTITUTE($B$2:$B$11;D2;"")))/LEN(D2)
Note: The above formula must be entered as an array formula. To enter a formula as an array formula, press Ctrl+Shift+Enter

[zbor]

Why not just:

Please Login or Register  to view this content.

[IMA_Saihat]

Hi,
The good thing about Excel Functions is that there are many ways to reach one result try this:
=SUMPRODUCT(--EXACT($B$2:$B$11;D2))
or
=SUMPRODUCT(EXACT($B$2:$B$11;D2)*1)

[IMA_Saihat]

Hi,
Another formula aiming the same result:
=SUM(IF($B$2:$B$11=D2;1;0))
or
=COUNTIF($B$2:$B$11;D2)

as "zbor" mentioned above.

[FDibbins]

IMA_Daihat, if you are posting tips and suggestions, please post them in that forum. If you have a question to ask, then please ask it

Thanks

[IMA_Saihat]

Hi, FDibbins
I am new to this forum, I do not know the Categories of the forum, now I understand.
Regards

[FDibbins]

Thank you