COUNTIF on a cell with commas..

[jimmy mcmahon]

Hi guys,

I have a range of cells that contain a list of names separated by commas..

i.e Cell G1 contains :- Sam Rower,John Kennelly,Richard Scott
Cell G2 contains :- Sam Rower,Richard West,Sam Rower,Bill Thomas
Cell G3 contains :- Sam Rower

I would like a column to the right of this that specifically counts the number of occurences of "Sam Rower" in the each cell....

The criteria being that Sam Rower will always have a comma immediately after it.

I have managed to do this for cell G3 with a simple countif formula, however it does not work with the comma separated cells. Any suggestions would be greatly appreciated

Many thanks

[Kyle123]

maybe:

Please Login or Register  to view this content.

[martindwilson]

put sam rower in say f1 then in h1 use
=(LEN(G1)-LEN(SUBSTITUTE(UPPER(G1),UPPER($F$1),"")))/LEN($F$1)
use of upper is to ignore case
if its case sensitive just use
=(LEN(G1)-LEN(SUBSTITUTE(G1,$F$1,"")))/LEN($F$1)