What FORMULA will count records that CONTAIN certain text?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
Back
Top