Identify number of items with characterisitics from two columns

[[email protected]]

I have two columns, one with dog color and the other with eye color. I
am trying to identify the number of grey dogs that have blue eyes. Dog
color is in one column, eye color is another. I'd be very grateful for
any help....just a PhD student trying to finish my dissertation.
thanks, judy
p.s. the specifics are made up but the problem has caused me an hour
and a half of frustration.

[paul]

sumproduct will do it for you
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
paul
remove nospam for email addy!



"[email protected]" wrote:

> I have two columns, one with dog color and the other with eye color. I
> am trying to identify the number of grey dogs that have blue eyes. Dog
> color is in one column, eye color is another. I'd be very grateful for
> any help....just a PhD student trying to finish my dissertation.
> thanks, judy
> p.s. the specifics are made up but the problem has caused me an hour
> and a half of frustration.
>
>

[davesexcel]

=SUM(IF((A1:A8=C1)*(B1:B8=D1),1,0))

"I am trying to identify the number of grey dogs that have blue eyes. "

this is an array formula so you need to
ctrl shift enter to get it to work

this example has the color of dogs in range A1:A8 and eye color in range B1:B8
C1 you can type in the dog color and D1 you can type in the eye color that you want to add up

Place the above formula in E1, don't forget to ctrl shift enter

[JudithJubilee]

Hello,

If you have your dog colour in A and your eye colour in B:

=SUMPRODUCT(--(A1:A100="Grey"),--(B1:B100="Blue"))

If you need more of the same it would be easier if you designated a cell for
each, ie. D1 for Dog Colour and E1 for Eye Colour you could have the
following:

=SUMPRODUCT(--(A1:A100=$D$1),--(B1:B100=$E$1))

You can then just type your new criteria in the D and E

Judith
--
Hope this helps


"[email protected]" wrote:

> I have two columns, one with dog color and the other with eye color. I
> am trying to identify the number of grey dogs that have blue eyes. Dog
> color is in one column, eye color is another. I'd be very grateful for
> any help....just a PhD student trying to finish my dissertation.
> thanks, judy
> p.s. the specifics are made up but the problem has caused me an hour
> and a half of frustration.
>
>

[L. Howard Kittle]

Try:

=SUM(IF(A1:A6=D1,IF(B1:B6=E1,1,0),0))

Where your list is in A & B
Where D1 is the color of the dog
Where E1 is the eye color

Use CTRL + SHIFT + ENTER to enter.

HTH
Regards,
Howard

<[email protected]> wrote in message
news:[email protected]...
>I have two columns, one with dog color and the other with eye color. I
> am trying to identify the number of grey dogs that have blue eyes. Dog
> color is in one column, eye color is another. I'd be very grateful for
> any help....just a PhD student trying to finish my dissertation.
> thanks, judy
> p.s. the specifics are made up but the problem has caused me an hour
> and a half of frustration.
>

[Pete_UK]

As an alternative if you want to do other comparisons, you might
consider using autofilters. You could filter for dog colour in one
column and for eye colour in another and this formula would give you a
count of the visible rows:

=SUBTOTAL(3,A3:A5000)

You could insert a new row at the very top of your spreadsheet and put
this formula there, so that it is always visible when you apply the
filters. I am assuming that you have a header row (which would become
row 2), so that your data begins in row 3. Adjust the range to suit.
Choosing other filters would allow you to record other counts.

Hope this helps.

Pete