count if two values are true

[pep]

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

 

[Don Guillett]

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

 

[Dave Peterson]

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().

 

[T. Valko]

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)

 

[Dave Peterson]

Thanks, Biff.

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