Microsoft Excel Hints and Tips
Question
I have a list of text in cells A1:A10 - I would like to count the number
of cells that contain the text "Apple" anywhere in the cell - How can this
be done ?
Solution
Lets say you have the following text in cells A1:A10
Apple
Peach and Apple
Pear
Apple
Apple and Peach
Pear
Apple
Peach
Pear and Apple
Apples and Apples
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:A10))=FALSE,1,0) ) and
then click inside your formula bar and press Ctrl + Shift + Enter
to make this an array formula.
Return to Excel Exchange homepage.