Count column b if column a equal cell value

[dicko1]

Hi all,

I am trying to return the count of values in column B based on column
A matching C1. For example:

A B C

1 Bob 11/4/2010 Bob
2 Bob 11/3/2010
3 Steve 11/4/2010
4 Sara 11/9/2010
5 Kim 11/9/2010
6 Kim 11/9/2010
7 Bob 11/3/2010
8 Steve 11/12/2010

I thought this should work but it doesnt. =countif(B1:B8,A1:A8=C1)

Any ideas?

Thanks,
Ryan

 

[Pete_UK]

This is the correct syntax for that:

=COUNTIF(A1:A8,C1)

i.e. count the number of values in column A which match C1.

Hope this helps.

Pete

 

[Gord Dibben]

Why not simply count the number of Bob's in A1:A8?

=COUNTIF(A1:A8,C1)

Returns 3


Gord Dibben MS Excel MVP

 

[dicko1]

This is the correct syntax for that:

=COUNTIF(A1:A8,C1)

i.e. count the number of values in column A which match C1.

Hope this helps.

Pete

RE:

I want to count the amount in Column B though. Column B will not
always have data in it whereas Column A will. So I really only want to
know that amount of dates there were for every time Bob shows up in
Column A

 

[Dave Peterson]

Maybe...

=SUMPRODUCT(--(A1:A8=C1),--(B1:B8<>""))

Adjust the ranges to match--but you can't use whole columns (except in xl2007+).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html


And if you're using xl2007+, you may want to look at =countifS() in Excel's help.

 

[Gord Dibben]

You pinned it Dave.


Gord

Maybe...

=SUMPRODUCT(--(A1:A8=C1),--(B1:B8<>""))

Adjust the ranges to match--but you can't use whole columns (except in xl2007+).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html


And if you're using xl2007+, you may want to look at =countifS() in Excel's help.

 

[dicko1]

Perfect!!!

Thanks,
Ryan

 

[Dave Peterson]

Sometimes, it's the interpretation that's the difficult part <vbg>.