What FORMULA will count records that CONTAIN certain text?

[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.

[CLR]

=COUNTIF(range,criteria)

Vaya con Dios,
Chuck, CABGx3



"jfrisch3" wrote:

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

[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:A10))=FALSE,1,0))
and then click inside your formula bar and press Ctrl+Shift+Enter to make this an array formula.

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

[kkrause]

Thank you, thank you, thank you! This formula performs beautifully where many of the others I researched failed. The simplicity of it is fantastic and the fact that it works tireless is a bonus! Thank you so very much!

[daddylonglegs]

Simpler to use wildcards with COUNTIF like this

=COUNTIF(A1:A10,"*Apple*")

[kkrause]

Simpler to use wildcards with COUNTIF like this

=COUNTIF(A1:A10,"*Apple*")

daddylonglegs -

Thanks for your response. Although I definitely see the benefit of using your formula, I like the other one better for several reasons: I don't like typing in the exact text into the formula that I am trying to find.

For ease of using the same example, here's what I mean:
I typed the word Apple into cell O17 in my sheet, then I changed the formula to be O17 instead of "Apple".
Former example: SUM(IF(ISERROR(SEARCH("Apple",A1:A10))=FALSE,1,0))
New Example: SUM(IF(ISERROR(SEARCH(O17,A1:A10))=FALSE,1,0))

That way, if I change the text I'm looking for in cell O17 from "Apple" to "Apples", or any other terminology (Oranges, Lemons, etc.), the formulas still work w/out any additional changes to the formula.

Thanks!

[daddylonglegs]

.....Well that will work with COUNTIF too......

=COUNTIF(A1:A10,"*"&O17&"*")

[kkrause]

Ahh! I like it! Thank you!