count occurrences before/less than given value across sheets

Hi all

I would like to ask for advice for following problem (Excel 2007)...

I have two sheets (tables). In sheet 1 I have data in two columns: column A has 4-digit identifiers for individuals (e.g. 9841, 8436, 7459), column B has data of years (e.g. 2003, 2004, 2005). Individuals may occur repeatedly if they had events in multiple years.

For example,
...

7459 1995

9841 2003
9841 2005
9841 2010

8436 2001
...

Then I have another sheet where I have the year of an event in column A (e.g. 2008). In a second column B I have individual identifiers again (e.g. 9841). In a third column C I would like to be able to calculate how many events are known in the first sheet for that individual before the event in column A. So in my example, how often does individual 9841 occur before 2008? The answer I would like to have computed: 2 (i.e. 2003 and 2005).

2008 9841

I have tried various things for quite some time now, including countifs and <, but for some reason it does not work.

Any thoughts?
Thanks in advance, N

In the other sheet,
you could try this in C2:
=SUMPRODUCT((Sheet1!$A$2:$A$100=B2)*(Sheet1!$B$2:$B$100<A2))

----
Success? Celebrate it, click the star at the bottom left of my response

Thank you, that helped.

N

The alternative available to you in Excel 2007 would be:

=COUNTIFS(Sheet1!A:A,B2,Sheet1!B:B,"<"&A2)