+ Reply to Thread
Results 1 to 8 of 8

Counting based upon 2 conditions that are text based

  1. #1
    Registered User
    Join Date
    05-28-2004
    Location
    New York City
    Posts
    20

    Counting based upon 2 conditions that are text based


    Hello everyone,
    I'm trying to count data based upon two columns of data that are text based. For example:
    Column A Column B
    1. dog blue
    2. cat Brown
    3. cow Brown
    4. cow White
    5. cat Black
    I'd like to know how I can count how many Cow's are brown. I'm sure there's a nested CountIF statement in there somewhere.

    Thanks for any help!
    Anita

  2. #2
    Dave F
    Guest

    RE: Counting based upon 2 conditions that are text based

    Well one easy cheat is to concatenate the two columns into one and then count
    how many concatenated text strings match your criteria.

    Example:

    =concatenate(a1,b1) would yield dogblue etc.

    so, =countif(c1:c5,"cowbrown") would return 1.
    --
    Brevity is the soul of wit.


    "walkerdayle" wrote:

    >
    >
    > Hello everyone,
    > I'm trying to count data based upon two columns of data that are text
    > based. For example:
    > Column A Column B
    >
    > - dog blue
    > - cat Brown
    > - cow Brown
    > - cow White
    > - cat Black
    >
    > I'd like to know how I can count how many Cow's are brown. I'm sure
    > there's a nested CountIF statement in there somewhere.
    >
    > Thanks for any help!
    > Anita
    >
    >
    > --
    > walkerdayle
    > ------------------------------------------------------------------------
    > walkerdayle's Profile: http://www.excelforum.com/member.php...o&userid=10021
    > View this thread: http://www.excelforum.com/showthread...hreadid=573823
    >
    >


  3. #3
    Pete_UK
    Guest

    Re: Counting based upon 2 conditions that are text based

    Try this array* formula:

    =SUM(IF((A1:A10="cow")*(B1:B10="brown"),1,0))

    * As this is an array formula, then once you have typed it in (or
    subsequently edit it) you must use CTRL-SHIFT-ENTER rather than just
    ENTER. If you do this correctly then Excel will wrap curly braces { }
    around the formula - you must not type these yourself.

    Adjust the ranges to suit your data (both should be the same size).

    Hope this helps.

    Pete

    walkerdayle wrote:
    >
    > Hello everyone,
    > I'm trying to count data based upon two columns of data that are text
    > based. For example:
    > Column A Column B
    >
    > - dog blue
    > - cat Brown
    > - cow Brown
    > - cow White
    > - cat Black
    >
    > I'd like to know how I can count how many Cow's are brown. I'm sure
    > there's a nested CountIF statement in there somewhere.
    >
    > Thanks for any help!
    > Anita
    >
    >
    > --
    > walkerdayle
    > ------------------------------------------------------------------------
    > walkerdayle's Profile: http://www.excelforum.com/member.php...o&userid=10021
    > View this thread: http://www.excelforum.com/showthread...hreadid=573823



  4. #4
    Bob Phillips
    Guest

    Re: Counting based upon 2 conditions that are text based

    =SUMPRODUCT(--(A1:A10="cow"),--(B1:B10="brown"))

    --
    HTH

    Bob Phillips

    (replace somewhere in email address with gmail if mailing direct)

    "walkerdayle" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    >
    > Hello everyone,
    > I'm trying to count data based upon two columns of data that are text
    > based. For example:
    > Column A Column B
    >
    > - dog blue
    > - cat Brown
    > - cow Brown
    > - cow White
    > - cat Black
    >
    > I'd like to know how I can count how many Cow's are brown. I'm sure
    > there's a nested CountIF statement in there somewhere.
    >
    > Thanks for any help!
    > Anita
    >
    >
    > --
    > walkerdayle
    > ------------------------------------------------------------------------
    > walkerdayle's Profile:

    http://www.excelforum.com/member.php...o&userid=10021
    > View this thread: http://www.excelforum.com/showthread...hreadid=573823
    >




  5. #5
    Bob Phillips
    Guest

    Re: Counting based upon 2 conditions that are text based

    You can shorten that to

    =SUM((A1:A10="cow")*(B1:B10="brown"))

    --
    HTH

    Bob Phillips

    (replace somewhere in email address with gmail if mailing direct)

    "Pete_UK" <[email protected]> wrote in message
    news:[email protected]...
    > Try this array* formula:
    >
    > =SUM(IF((A1:A10="cow")*(B1:B10="brown"),1,0))
    >
    > * As this is an array formula, then once you have typed it in (or
    > subsequently edit it) you must use CTRL-SHIFT-ENTER rather than just
    > ENTER. If you do this correctly then Excel will wrap curly braces { }
    > around the formula - you must not type these yourself.
    >
    > Adjust the ranges to suit your data (both should be the same size).
    >
    > Hope this helps.
    >
    > Pete
    >
    > walkerdayle wrote:
    > >
    > > Hello everyone,
    > > I'm trying to count data based upon two columns of data that are text
    > > based. For example:
    > > Column A Column B
    > >
    > > - dog blue
    > > - cat Brown
    > > - cow Brown
    > > - cow White
    > > - cat Black
    > >
    > > I'd like to know how I can count how many Cow's are brown. I'm sure
    > > there's a nested CountIF statement in there somewhere.
    > >
    > > Thanks for any help!
    > > Anita
    > >
    > >
    > > --
    > > walkerdayle
    > > ------------------------------------------------------------------------
    > > walkerdayle's Profile:

    http://www.excelforum.com/member.php...o&userid=10021
    > > View this thread:

    http://www.excelforum.com/showthread...hreadid=573823
    >




  6. #6
    Registered User
    Join Date
    05-28-2004
    Location
    New York City
    Posts
    20

    Thank YOU!!!


    Thank you all for helping me. I found the sum array formula to work best. Thank you once again!!! It worked! Now I don't have to call Ghost Busters when I have a problem

    But I do have a question for Pete_UK, what was the 1,0 for at the end of the formula?

    Anita

  7. #7
    Bob Phillips
    Guest

    Re: Counting based upon 2 conditions that are text based

    it was to add a 1 in the condition was true, 0 if false. As I said, it was
    unnecessary.

    --
    HTH

    Bob Phillips

    (replace somewhere in email address with gmail if mailing direct)

    "Bob Phillips" <[email protected]> wrote in message
    news:[email protected]...
    > =SUMPRODUCT(--(A1:A10="cow"),--(B1:B10="brown"))
    >
    > --
    > HTH
    >
    > Bob Phillips
    >
    > (replace somewhere in email address with gmail if mailing direct)
    >
    > "walkerdayle" <[email protected]>
    > wrote in message
    > news:[email protected]...
    > >
    > >
    > > Hello everyone,
    > > I'm trying to count data based upon two columns of data that are text
    > > based. For example:
    > > Column A Column B
    > >
    > > - dog blue
    > > - cat Brown
    > > - cow Brown
    > > - cow White
    > > - cat Black
    > >
    > > I'd like to know how I can count how many Cow's are brown. I'm sure
    > > there's a nested CountIF statement in there somewhere.
    > >
    > > Thanks for any help!
    > > Anita
    > >
    > >
    > > --
    > > walkerdayle
    > > ------------------------------------------------------------------------
    > > walkerdayle's Profile:

    > http://www.excelforum.com/member.php...o&userid=10021
    > > View this thread:

    http://www.excelforum.com/showthread...hreadid=573823
    > >

    >
    >




  8. #8
    Pete_UK
    Guest

    Re: Counting based upon 2 conditions that are text based

    Thanks Bob, it's obvious now that you've pointed it out !!

    Pete

    Bob Phillips wrote:
    > it was to add a 1 in the condition was true, 0 if false. As I said, it was
    > unnecessary.
    >
    > --
    > HTH
    >
    > Bob Phillips
    >
    > (replace somewhere in email address with gmail if mailing direct)
    >
    > "Bob Phillips" <[email protected]> wrote in message
    > news:[email protected]...
    > > =SUMPRODUCT(--(A1:A10="cow"),--(B1:B10="brown"))
    > >
    > > --
    > > HTH
    > >
    > > Bob Phillips
    > >
    > > (replace somewhere in email address with gmail if mailing direct)
    > >
    > > "walkerdayle" <[email protected]>
    > > wrote in message
    > > news:[email protected]...
    > > >
    > > >
    > > > Hello everyone,
    > > > I'm trying to count data based upon two columns of data that are text
    > > > based. For example:
    > > > Column A Column B
    > > >
    > > > - dog blue
    > > > - cat Brown
    > > > - cow Brown
    > > > - cow White
    > > > - cat Black
    > > >
    > > > I'd like to know how I can count how many Cow's are brown. I'm sure
    > > > there's a nested CountIF statement in there somewhere.
    > > >
    > > > Thanks for any help!
    > > > Anita
    > > >
    > > >
    > > > --
    > > > walkerdayle
    > > > ------------------------------------------------------------------------
    > > > walkerdayle's Profile:

    > > http://www.excelforum.com/member.php...o&userid=10021
    > > > View this thread:

    > http://www.excelforum.com/showthread...hreadid=573823
    > > >

    > >
    > >



+ 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