Counting Data from Another Worksheet - Countif

[quiksilver]

Okay, I have data on two worksheets. Two columns on each worksheet, one
is "NAME" and one is "DATE".

Sheet1:

NAME DATE
BOB 7/31/06
STEVE 7/31/06
JOE 7/31/06
KEVIN 7/31/06

Sheet2:

NAME DATE
BOB 8/15/06
BOB 7/15/06
BOB 6/15/06
STEVE 9/15/06
STEVE 8/15/06
STEVE 7/15/06
STEVE 6/15/06
JOE 5/15/06
JOE 4/15/06
KEVIN 7/15/06
KEVIN 6/15/06
KEVIN 5/15/06

So what I want is a formula on each row on Sheet1 that tells me how
many times a particular name from Sheet1 has a corresponding date on
Sheet2 that occurs before the date on Sheet1. For instance, the first
row on Sheet1 has Bob 7/31/06. So I'd want to know how many dates for
Bob on Sheet2 occur before 7/31/06.

If I do COUNTIF(Sheet2!B2:B4,"<="&b2) it returns the correct result,
but I have to change the range it's looking manually for each line.
How do I get it to find it's own range based on the Name from the first
sheet?

Thanks in advance.

 

[Dave Peterson]

I put this in C2 of Sheet1:

=SUMPRODUCT(--(Sheet2!$A$2:$A$13=A2),--(Sheet2!$B$2:$B$13<B2))

Adjust the ranges to match--but you can't use whole columns.

=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

========
You said before, but you use <= in your sample. I'm not sure what you really
want.

 

[quiksilver]

You said before, but you use <= in your sample. I'm not sure what you
really
want.

Yeah, I should have said "on or before". How would that change the
formula?

 

[Dave Peterson]

change the < to <=

Yeah, I should have said "on or before". How would that change the
formula?