Counting based upon 2 conditions that are text based

[walkerdayle]

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

 

[Guest]

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.

 

[Pete_UK]

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

 

[Bob Phillips]

=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]...

 

[Bob Phillips]

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)

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]

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

 

[Bob Phillips]

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)

=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

 

[Pete_UK]

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

Pete

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)