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

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

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

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

--
HTH

Bob Phillips

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

"walkerdayle"
wrote in message
...


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

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" wrote in message
oups.com...
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

:)
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


--
walkerdayle
------------------------------------------------------------------------
walkerdayle's Profile: http://www.excelforum.com/member.php...o&userid=10021
View this thread: http://www.excelforum.com/showthread...hreadid=573823

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" wrote in message
...
=SUMPRODUCT(--(A1:A10="cow"),--(B1:B10="brown"))

--
HTH

Bob Phillips

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

"walkerdayle"
wrote in message
...


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

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" wrote in message
...
=SUMPRODUCT(--(A1:A10="cow"),--(B1:B10="brown"))

--
HTH

Bob Phillips

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

"walkerdayle"
wrote in message
...


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