+ Reply to Thread
Results 1 to 21 of 21

Colomn of strings, how to count each string with "abc" OR "def"

  1. #1
    Ron Rosenfeld
    Guest

    Re: Colomn of strings, how to count each string with "abc" OR "def"

    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

  2. #2
    Tommy T
    Guest

    Re: Colomn of strings, how to count each string with "abc" OR "def

    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
    >


  3. #3
    Ron Rosenfeld
    Guest

    Re: Colomn of strings, how to count each string with "abc" OR "def

    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

  4. #4
    Tommy T
    Guest

    Re: Colomn of strings, how to count each string with "abc" OR "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
    >
    >


  5. #5
    Domenic
    Guest

    Re: Colomn of strings, how to count each string with "abc" OR "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!


  6. #6
    Ron Rosenfeld
    Guest

    Re: Colomn of strings, how to count each string with "abc" OR "def

    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

  7. #7
    Domenic
    Guest

    Re: Colomn of strings, how to count each string with "abc" OR "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!


  8. #8
    Tommy T
    Guest

    Re: Colomn of strings, how to count each string with "abc" OR "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
    >
    >


  9. #9
    Tommy T
    Guest

    Re: Colomn of strings, how to count each string with "abc" OR "def

    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
    >


  10. #10
    Ron Rosenfeld
    Guest

    Re: Colomn of strings, how to count each string with "abc" OR "def"

    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

  11. #11
    Domenic
    Guest

    Re: Colomn of strings, how to count each string with "abc" OR "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!


  12. #12
    Ron Rosenfeld
    Guest

    Re: Colomn of strings, how to count each string with "abc" OR "def

    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

  13. #13
    Tommy T
    Guest

    Re: Colomn of strings, how to count each string with "abc" OR "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
    >
    >


  14. #14
    Tommy T
    Guest

    Re: Colomn of strings, how to count each string with "abc" OR "def

    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
    >


  15. #15
    Ron Rosenfeld
    Guest

    Re: Colomn of strings, how to count each string with "abc" OR "def"

    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

  16. #16
    Tommy T
    Guest

    Re: Colomn of strings, how to count each string with "abc" OR "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
    >
    >


  17. #17
    Domenic
    Guest

    Re: Colomn of strings, how to count each string with "abc" OR "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!


  18. #18
    Ron Rosenfeld
    Guest

    Re: Colomn of strings, how to count each string with "abc" OR "def

    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

  19. #19
    Tommy T
    Guest

    Re: Colomn of strings, how to count each string with "abc" OR "def

    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
    >


  20. #20
    Ron Rosenfeld
    Guest

    Re: Colomn of strings, how to count each string with "abc" OR "def"

    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

  21. #21
    Tommy T
    Guest

    Colomn of strings, how to count each string with "abc" OR "def"

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1