Formula help needed if possible!?

[GD]

Hi there, i'm attempting to implement a cross-sheet formula to count the
occurances of a single phrase or grouping in one sheet, producing a simple
total figure in a cell in another.
To put it into real terms, in one sheet there is a collumn with a series of
initials indicating an occurence related to their performance - which I am
looking to produce a small scorecard in a separate sheet for each user.
Eh Sheet 1 - B7:B47 have phrases such as GD (KS), AG (KS) etc etc etc - I
need a formula which counts the occurances of a specified such phrase, say GD
(KS) and totals the figure in Sheet 2, for instance if GD (KS) is present 5
times, I need the formula to simply count this and read 5
Ive tried using =SUM(LEN(G21:G25)-LEN(SUBSTITUTE(G21:G25,"GD (KS)",""))) but
for some reason it only reacts when the phrase is put in one particular cell,
and reather than reading 1 as if it was only counting that, it produces the
number 2!?

Any help would be greatfuly recieved, cheers

 

[Bob Phillips]

try this

=SUMPRODUCT(--(LEN(G21:G25)-LEN(SUBSTITUTE(G21:G25,"GD (KS)",""))>0))

or this case-sensitive version

=SUMPRODUCT(--(ISNUMBER(FIND("GD (KS)",G21:G25))))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[GD]

Bob that's fantastic, thanks ever so much for the help.