Counting instances that 2 different columns match
 

I am trying to enter a formula to do the following:

I am trying to have one cell count the number of times a name appears in one
column while a value also appears in another alongside of it

Ex:

Column A Column B
Bill 1
Steve 1
Harry 0
Bill 1
Harry 1
Bill 0
Steve 1
........

I am looking for a formula that tells me how many times Bill appears in
column A and 1 appears in column B alongside it (has to be both Bill and 1)

The example above should give me a result of 2

Any suggestions?

Counting instances that 2 different columns match
 

=SUMPRODUCT(--(A1:A100="Bill"), --(B1:B100=1))
or
=SUMPRODUCT(--(A1:A100=C1), --(B1:B100=D1)) where C1 and D1 hold the names
and number to count

For explanation see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email


"Cheech is Lost" <Cheech is wrote in message
...
I am trying to enter a formula to do the following:

I am trying to have one cell count the number of times a name appears in
one
column while a value also appears in another alongside of it

Ex:

Column A Column B
Bill 1
Steve 1
Harry 0
Bill 1
Harry 1
Bill 0
Steve 1
.......

I am looking for a formula that tells me how many times Bill appears in
column A and 1 appears in column B alongside it (has to be both Bill and
1)

The example above should give me a result of 2

Any suggestions?

Counting instances that 2 different columns match
 

Worked geat!

Thanks for your help!

"Bernard Liengme" wrote:

=SUMPRODUCT(--(A1:A100="Bill"), --(B1:B100=1))
or
=SUMPRODUCT(--(A1:A100=C1), --(B1:B100=D1)) where C1 and D1 hold the names
and number to count

For explanation see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email


"Cheech is Lost" <Cheech is wrote in message
...
I am trying to enter a formula to do the following:

I am trying to have one cell count the number of times a name appears in
one
column while a value also appears in another alongside of it

Ex:

Column A Column B
Bill 1
Steve 1
Harry 0
Bill 1
Harry 1
Bill 0
Steve 1
.......

I am looking for a formula that tells me how many times Bill appears in
column A and 1 appears in column B alongside it (has to be both Bill and
1)

The example above should give me a result of 2

Any suggestions?