countif, again

[Liz G]

My original question:
I have a countif formula (see post: variation on countif) that reads
Countif(A1:A100,"*value*"). The situation is that users were asked to list
their past isp's and could list more than one in a cell. So, is there a way
to say "Count this cell if the cell includes either 'aol', 'america online',
or 'america on-line'"?

The reply:
one way: just add the COUNTIF statements.
Another way:
=SUM(COUNTIF(A1:A100,{"*aol*","*America Online*","*america on-lin*"}))

Next question:
The only thing is, I want to make sure that people who replied with
something like "america online (aol)" don't get counted twice. That's the
reason behind this question.

Thanks,
Liz

 

[Frank Kabel]

Hi
try:
=SUMPRODUCT(--(ISNUMBER(SEARCH("aol",A1:A100))+ISNUMBER(SEARCH("America
Online",A1:A100))+ISNUMBER(SEARCH("america on-line",A1:A100))>0))

 

[Myrna Larson]

You could subtract the count for cells that contain "(aol)" -- with the
parentheses.