count if two values are true

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pep - 26 Jan 2008 18:05 GMT

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

Reply to this Message

Don Guillett - 26 Jan 2008 18:22 GMT

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

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Hi,
>
[quoted text clipped - 11 lines]
>
> -Peter

Reply to this Message

Dave Peterson - 26 Jan 2008 19:13 GMT

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

> Hi,
>
[quoted text clipped - 10 lines]
>
> -Peter

Signature

Dave Peterson

Reply to this Message

T. Valko - 26 Jan 2008 19:52 GMT

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)

Signature

Biff
Microsoft Excel MVP

> One more:
>
[quoted text clipped - 31 lines]
>>
>> -Peter

Reply to this Message

Dave Peterson - 26 Jan 2008 20:10 GMT

Thanks, Biff.

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

> In Excel 2007:
>
[quoted text clipped - 51 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message