Counting based upon 2 conditions that are text based

  • Thread starter Thread starter walkerdayle
  • Start date Start date
W

walkerdayle

: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
 
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.
 
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
 
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 said:
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
Click to expand...
 
:)
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
 
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 said:
=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
Click to expand...
 
Thanks Bob, it's obvious now that you've pointed it out !!

Pete

Bob said:
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)
Click to expand...
 
Back
Top