Re: count occurances of string

["Wall, Peter" <Peter.Wall@SNCLAVALIN.COM>]

Andy

Try array formula

{=SUM(IF(ISERROR(SEARCH(A1,A200:BZ200)),,1))}

Regards,
Peter Wall
Perth


-----Original Message-----
From: Andy Armstrong [mailto:a.armstrong@SHOTOVERJET.CO.NZ]
Sent: Thursday, 24 July 2003 9:52 AM
To: EXCEL-G@PEACH.EASE.LSOFT.COM
Subject: count occurances of string

Hi All
In a row say A200:BZ200 I have in each cell the answers to a question in a
survey.  Cell A200 might be "Seeing the chicks" cells AS200 might be
"photograph the chickens" cell BA might be "Photo"

Can I use a COUNTIF somehow to add up the number of cells in the range
A200:BZ200 that contain the string a put in cell A1 - for example in cell A1
I type the text "Photo" the result should count all occurances of "photo",
"photograph",  "photography"

Later I want to change A1 to say "chic" and "chicks" and "chicken" should be
counted as would of course any string with the text "chic" in it

Ideally I do not want it to be case sensitive, and the characters in A1
could appear at the end of the cell or the beginning.

All help will be appreciated by thousands
FYI - excel 2000 v9, a VBA solution would be interesting to see but a
formula easier for me to understand

Regards

Andy

--------------------------------------------------------------------------
The EXCEL-G list is hosted on a Windows NT(TM) machine running L-Soft
international's LISTSERV(R) software.  For subscription/signoff info
and archives, see http://peach.ease.lsoft.com/archives/excel-g.html .
                             COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G

--------------------------------------------------------------------------
The EXCEL-G list is hosted on a Windows NT(TM) machine running L-Soft
international's LISTSERV(R) software.  For subscription/signoff info
and archives, see http://peach.ease.lsoft.com/archives/excel-g.html .
                             COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G