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
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel formula to count the number of records which contains a certaintext (wildcard) 3
Function to determine if a cell contains a formula 5
counting 2
Count unique records that also meet criteria 3
counting formula 8
Subtotal counting formula in cells 1
Average if questions 2
Excel Count Cells That Contain Text With "?" 0

Back
Top