Hi,

I have in A column hundreds of dates. B column has hudreds of names.

A B
11.01.2008 Tim
12.01.2008 Paul
12.01.2008 Jack
14.01.2008 Paul
......

I would like to count how many times e.g. Paul is mentioned in January 2008.

-Peter

=sumproduct((month(a2:a22)=1)*(b2:b22="Paul"))
to add the year
=sumproduct((year(a2:a22)=2008)*(month(a2:a22)=1)*(b2:b22="Paul"))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"pep" <(E-Mail Removed)> wrote in message
news:IDKmj.287328$(E-Mail Removed)...
> Hi,
>
> I have in A column hundreds of dates. B column has hudreds of names.
>
> A B
> 11.01.2008 Tim
> 12.01.2008 Paul
> 12.01.2008 Jack
> 14.01.2008 Paul
> .....
>
> I would like to count how many times e.g. Paul is mentioned in January
> 2008.
>
> -Peter
>

One more:

=sumproduct(--(text(a1:a100,"yyyymm")="200801"),--(b1:b100="Paul"))

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

====
If you're using xl2007, you should look at excel's help for =countifs().


pep wrote:
>
> Hi,
>
> I have in A column hundreds of dates. B column has hudreds of names.
>
> A B
> 11.01.2008 Tim
> 12.01.2008 Paul
> 12.01.2008 Jack
> 14.01.2008 Paul
> .....
>
> I would like to count how many times e.g. Paul is mentioned in January 2008.
>
> -Peter

--

Dave Peterson

In Excel 2007:

=COUNTIFS(A1:A10,">="&DATE(2008,1,1),A1:A10,"<="&DATE(2008,1,31),B1:B10,"Paul")

Or, using cells to hold the criteria:

D1 = 1/1/2008
E1 = 1/31/2008
F1 = Paul

=COUNTIFS(A1:A10,">="&D1,A1:A10,"<="&E1,B1:B10,F1)

--
Biff
Microsoft Excel MVP


"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> One more:
>
> =sumproduct(--(text(a1:a100,"yyyymm")="200801"),--(b1:b100="Paul"))
>
> 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
>
> ====
> If you're using xl2007, you should look at excel's help for =countifs().
>
>
> pep wrote:
>>
>> Hi,
>>
>> I have in A column hundreds of dates. B column has hudreds of names.
>>
>> A B
>> 11.01.2008 Tim
>> 12.01.2008 Paul
>> 12.01.2008 Jack
>> 14.01.2008 Paul
>> .....
>>
>> I would like to count how many times e.g. Paul is mentioned in January
>> 2008.
>>
>> -Peter
>
> --
>
> Dave Peterson

Thanks, Biff.

And from what I've read, =countifs() and =sumifs() are faster than =sumproduct()
and can use the whole column.



"T. Valko" wrote:
>
> In Excel 2007:
>
> =COUNTIFS(A1:A10,">="&DATE(2008,1,1),A1:A10,"<="&DATE(2008,1,31),B1:B10,"Paul")
>
> Or, using cells to hold the criteria:
>
> D1 = 1/1/2008
> E1 = 1/31/2008
> F1 = Paul
>
> =COUNTIFS(A1:A10,">="&D1,A1:A10,"<="&E1,B1:B10,F1)
>
> --
> Biff
> Microsoft Excel MVP
>
> "Dave Peterson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > One more:
> >
> > =sumproduct(--(text(a1:a100,"yyyymm")="200801"),--(b1:b100="Paul"))
> >
> > 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
> >
> > ====
> > If you're using xl2007, you should look at excel's help for =countifs().
> >
> >
> > pep wrote:
> >>
> >> Hi,
> >>
> >> I have in A column hundreds of dates. B column has hudreds of names.
> >>
> >> A B
> >> 11.01.2008 Tim
> >> 12.01.2008 Paul
> >> 12.01.2008 Jack
> >> 14.01.2008 Paul
> >> .....
> >>
> >> I would like to count how many times e.g. Paul is mentioned in January
> >> 2008.
> >>
> >> -Peter
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson