Counting text occurances in a range

[Alwyn M]

Hi

I have a column of text that looks simmilar to the following:

Column B
S
SS
G
M
MMM
S
GG

The data within the column can change. I need to count the number of "S"'s that occur as well as the "G"'s and the "M"'s. The "S" stands for Shop, while the M is for mobile units and G's are for Gazebo units. It would be the easy way out to use a numeric for the number of units the franchisees have, but management wants to see the MG or S's. For the above data i need a cell values for the "S" = 4, "G"=3 and "M"=4. First thought of concatenating the range and then counting but having some difficulty as the values can change and it needs to be responsive. Any help would be greatly appreciated.

Many thanks, Alwyn

 

[Smallman]

Hi Alwyn

See attached example, showing the results

Take care

Smallman

 

[Hui]

Alwyn

Firstly, Welcome to the Chandoo.org Forums

I will assume that your data is in B2:B8
In D2:D4 put S, M, G

in E2: =SUMPRODUCT(LEN($B$2:$B$8)-LEN(SUBSTITUTE($B$2:$B$8,$D2,"")))

Copy E2 Down



See attached file:

 

[Khalid NGO]

Hi Alwyn,
Welcome to the forum...

you can try a simple formula =countif(range,"S")
or see the file attached...

 

[Alwyn M]

HI Guys

Many thanks for the prompt help. It worked. You guys are magic. Thanks all.

 

[Hui]

Khalid

Countif/s wont count multiple occurrences of say S in one cell as requested by Alwyn

Have a browse through Smallmans and my post to see how to tackle that issue

 

[Somendra Misra]

@Hui

Awesome simple Hui, learnt something new.

Regards,

 

[Khalid NGO]

Khalid

Countif/s wont count multiple occurrences of say S in one cell as requested by Alwyn

Have a browse through Smallmans and my post to see how to tackle that issue

Thanks Sir Hui,
These things are new for me...


SUMPRODUCT and ARRAYs always confused me
I should learn these seriously.