#1   Report Post  
Old November 1st 04, 09:23 PM
Liz G
 
Posts: n/a
Default 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'"?

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



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

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

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


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

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

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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
CountIF cells are not empty Wayne Excel Discussion (Misc queries) 3 January 6th 05 04:44 PM
Subtotal And Countif Sheryl Excel Discussion (Misc queries) 4 December 9th 04 07:18 PM
combining countif formulas Liz G Excel Worksheet Functions 3 November 1st 04 09:34 PM
COUNTIF Question zbert Excel Worksheet Functions 2 October 31st 04 05:14 PM
CountIF across multiple sheets in a workbook Al Excel Worksheet Functions 1 October 29th 04 01:15 PM


All times are GMT +1. The time now is 06:47 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017