Count column b if column a equal cell value

D

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
 
P

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
 
G

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
 
D

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
 
D

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.
 
G

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top