Remember Me?

#1
November 1st 04, 09:23 PM
 Liz G Posts: n/a
countif, again

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'"?

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

#2
November 1st 04, 09:32 PM
 Frank Kabel Posts: n/a

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

--
Regards
Frank Kabel
Frankfurt, Germany

Liz G wrote:
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'"?

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

#3
November 1st 04, 11:20 PM
 Myrna Larson Posts: n/a

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

On Mon, 1 Nov 2004 16:23:42 -0500, "Liz G" wrote:

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'"?

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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Wayne Excel Discussion (Misc queries) 3 January 6th 05 04:44 PM Sheryl Excel Discussion (Misc queries) 4 December 9th 04 07:18 PM Liz G Excel Worksheet Functions 3 November 1st 04 09:34 PM zbert Excel Worksheet Functions 2 October 31st 04 05:14 PM Al Excel Worksheet Functions 1 October 29th 04 01:15 PM

All times are GMT +1. The time now is 09:41 PM.