What FORMULA will count records that CONTAIN certain text?

J

jfrisch3

I have a survey that allows mulitple answers. What formula can I use to
count the records that contain certain text? I can Filter the responses for
those records that contain the data, but I want to use a formula instead.
 
M

michael.a7

Lets say you have the following in cells A1:A10
Apple
PeachApple
Pear
Apple
ApplePeach
Pear
Apple
Peach
PearApple
AppleApple

You want to count those cells that contain the text Apple - so you want
the result to be 7.

Type =SUM(IF(ISERROR(SEARCH("Apple",A1:A15))=FALSE,1,0))
and then click inside you formula bar and press Ctrl+Shift+Enter to
make this an array formula.

Regards,
Michael.
http://www.excelexchange.com
 
Top