Count column b if column a equal cell value

's Computer Specifications

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

's Computer Specifications

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

On Dec 2, 6:25*pm, dicko1 <dic...@hotmail.com> wrote:
> 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

's Computer Specifications

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

=COUNTIF(A1:A8,C1)

Returns 3

Gord Dibben MS Excel MVP

On Thu, 2 Dec 2010 10:25:21 -0800 (PST), dicko1 <(E-Mail Removed)> wrote:

>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

's Computer Specifications

On Dec 2, 11:51*am, Pete_UK <pashu...@auditel.net> wrote:
> 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
>
> On Dec 2, 6:25*pm, dicko1 <dic...@hotmail.com> wrote:
>
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

's Computer Specifications

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.

On 12/02/2010 12:25, dicko1 wrote:
> 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
>

--
Dave Peterson

's Computer Specifications

You pinned it Dave.

Gord

On Thu, 02 Dec 2010 14:45:13 -0600, Dave Peterson <(E-Mail Removed)>
wrote:

>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.
>
>On 12/02/2010 12:25, dicko1 wrote:
>> 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
>>

's Computer Specifications

Perfect!!!

Thanks,
Ryan

's Computer Specifications

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

On 12/02/2010 15:38, Gord Dibben wrote:
> You pinned it Dave.
>
>
> Gord
>
> On Thu, 02 Dec 2010 14:45:13 -0600, Dave Peterson<(E-Mail Removed)>
> wrote:
>
>> 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.
>>
>> On 12/02/2010 12:25, dicko1 wrote:
>>> 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
>>>

--
Dave Peterson

Thread Tools
Show Printable Version Email this Page
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
count entries that equal one criteria if another column meets anot	ccKennedy	Microsoft Excel Worksheet Functions	7	24th Jan 2009 09:23 PM
How to check for a date range match in one column and then count thevalues equal to in another	Jon	Microsoft Excel Worksheet Functions	5	18th Jan 2008 11:54 AM
Count number of values equal to MAX of a column	dauclair	Microsoft Excel Misc	1	30th May 2006 02:23 PM
Count equal numbers in beginning of a column	=?Utf-8?B?RmlkZGxlck9uVGhlUm9vZg==?=	Microsoft Excel Programming	5	25th Jan 2006 07:08 AM
To have a 'fixed cell' be equal to the last data entered cell in a column	Mathew P Bennett	Microsoft Excel Misc	2	22nd Aug 2003 06:09 PM