I have a colomn of alpha-numeric strings and need to count cells which
contain phrase A or phrase B buried within them. This is driving me crazy,
please help!
I have a colomn of alpha-numeric strings and need to count cells which
contain phrase A or phrase B buried within them. This is driving me crazy,
please help!
Hi TommyOriginally Posted by Tommy T
Try this > =COUNTIF(A1:A2,"*abc*") change the range A1:A2 to suit your data, change abc to def to count the other option
Paul
On Mon, 5 Sep 2005 04:01:02 -0700, "Tommy T" <Tommy
[email protected]> wrote:
>I have a colomn of alpha-numeric strings and need to count cells which
>contain phrase A or phrase B buried within them. This is driving me crazy,
>please help!
=COUNTIF(A:A,"*abc*")
or, if your string to search for is in a cell:
=COUNTIF(A:A,"*"&D2&"*")
--ron
Thanks Ron,
I'd managed to get that far but the trouble is that I need the cell to be
counted if there is "abc" OR "def" within it. It's the OR bit which is
causing me trouble.
Any ideas?
Tom
"Ron Rosenfeld" wrote:
> On Mon, 5 Sep 2005 04:01:02 -0700, "Tommy T" <Tommy
> [email protected]> wrote:
>
> >I have a colomn of alpha-numeric strings and need to count cells which
> >contain phrase A or phrase B buried within them. This is driving me crazy,
> >please help!
>
>
> =COUNTIF(A:A,"*abc*")
>
> or, if your string to search for is in a cell:
>
> =COUNTIF(A:A,"*"&D2&"*")
>
>
> --ron
>
[QUOTE=Tommy T]Thanks Ron,
I'd managed to get that far but the trouble is that I need the cell to be
counted if there is "abc" OR "def" within it. It's the OR bit which is
causing me trouble.
Any ideas?
Hi Tommy
Try this > =SUM((COUNTIF(A:A,"*abc*")+(COUNTIF(A:A,"*def*"))))
Thanks Paul,
Trouble is that some of the cells contain both phrases, if I have two
different countif functions then they get double counted which I'm trying to
avoid.
Tom
"Paul Sheppard" wrote:
>
> Tommy T Wrote:
> > I have a colomn of alpha-numeric strings and need to count cells which
> > contain phrase A or phrase B buried within them. This is driving me
> > crazy,
> > please help!
>
> Hi Tommy
>
> Try this > =COUNTIF(A1:A2,"*abc*") change the range A1:A2 to suit your
> data, change abc to def to count the other option
>
>
> --
> Paul Sheppard
>
>
> ------------------------------------------------------------------------
> Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
> View this thread: http://www.excelforum.com/showthread...hreadid=401899
>
>
Hi TommyOriginally Posted by Tommy T
Try Data > Filter > Autofilter > Custom filter > contains abc > or contains def
Try...
=SUMPRODUCT(--(ISNUMBER(SEARCH("abc",A1:A100))+ISNUMBER(SEARCH("def",A1:A
100))>0))
or
=SUMPRODUCT(--(ISNUMBER(SEARCH(B1,A1:A100))+ISNUMBER(SEARCH(C1,A1:A100))>
0))
....where B1 contains your first criterion, such as abc, and C1 contains
your second criterion, such as def.
Hope this helps!
In article <[email protected]>,
"Tommy T" <Tommy [email protected]> wrote:
> I have a colomn of alpha-numeric strings and need to count cells which
> contain phrase A or phrase B buried within them. This is driving me crazy,
> please help!
On Mon, 5 Sep 2005 04:46:01 -0700, "Tommy T" <[email protected]>
wrote:
>Thanks Ron,
>
>I'd managed to get that far but the trouble is that I need the cell to be
>counted if there is "abc" OR "def" within it. It's the OR bit which is
>causing me trouble.
>
>Any ideas?
>
>Tom
>
>"Ron Rosenfeld" wrote:
>
>> On Mon, 5 Sep 2005 04:01:02 -0700, "Tommy T" <Tommy
>> [email protected]> wrote:
>>
>> >I have a colomn of alpha-numeric strings and need to count cells which
>> >contain phrase A or phrase B buried within them. This is driving me crazy,
>> >please help!
>>
>>
>> =COUNTIF(A:A,"*abc*")
>>
>> or, if your string to search for is in a cell:
>>
>> =COUNTIF(A:A,"*"&D2&"*")
>>
>>
>> --ron
>>
=SUM(COUNTIF(A:A,{"*abc*","*def*"}))
will count each cell that has one or the other string. However, it will double
count cells that have both strings: e.g. 12abc34def56 would get counted
twice.
If you don't want double counting, then:
=SUM(COUNTIF(A:A,{"*abc*","*def*"}),-COUNTIF(A:A,"*abc*def*"),-COUNTIF(A:A,"*def*abc*"))
--ron
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks