Counting based upon 2 conditions that are text based
 

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

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:


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

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

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

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

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" 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:
:confused:
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

Counting based upon 2 conditions that are text based
 

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

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" 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
...

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

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" 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
...

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