# Counting based upon 2 conditions that are text based

1. ## 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. ## 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
>
>

3. ## 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

4. ## 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" <walkerdayle.2cwe75_1156176907.8505@excelforum-nospam.com>
wrote in message
news:walkerdayle.2cwe75_1156176907.8505@excelforum-nospam.com...
>
>
> 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
>

5. ## 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" <pashurst@auditel.net> wrote in message
> 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

>

6. ## 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. ## 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" <bob.NGs@somewhere.com> wrote in message
news:Oap6PgUxGHA.5056@TK2MSFTNGP06.phx.gbl...
> =SUMPRODUCT(--(A1:A10="cow"),--(B1:B10="brown"))
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "walkerdayle" <walkerdayle.2cwe75_1156176907.8505@excelforum-nospam.com>
> wrote in message
> news:walkerdayle.2cwe75_1156176907.8505@excelforum-nospam.com...
> >
> >
> > 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

> >

>
>

8. ## 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" <bob.NGs@somewhere.com> wrote in message
> news:Oap6PgUxGHA.5056@TK2MSFTNGP06.phx.gbl...
> > =SUMPRODUCT(--(A1:A10="cow"),--(B1:B10="brown"))
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "walkerdayle" <walkerdayle.2cwe75_1156176907.8505@excelforum-nospam.com>
> > wrote in message
> > news:walkerdayle.2cwe75_1156176907.8505@excelforum-nospam.com...
> > >
> > >
> > > 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:

> > >

> >
> >

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

#### 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