I see you had two replies yesterday (from people more expert than myself) ,
both of which indicate to me the difficulty we are having; not least because
the values in B in your example are 7 digits whereas in your original post
you said the cells in A contained 6 digit numbers!

So my attempt assumes the following:

EACH cell in A1:A200 contains 20 numbers of 6/7 digits separated by a blank.
I look for maches in the whole range for a given B value i.e. match B1
against ALL A1:A200 (4000 numbers (200 *20) and then check the C range to see
if it matches a year.

Again, this only makes sense if the same B value occurs for more than one
year; your sample data doesn't show this.

In the example below, it finds values where year is <=2001

If there are still problems, mail me a workbook with an actual example and
result to

=SUMPRODUCT(--(LEN($A$1:$A$200)-LEN(SUBSTITUTE($A$1:$A$200,$B1,"")))/LEN($B1),--($C$1:$C$200=2001))

Put this in D1 and copy down.

"Tomac" wrote:


4673923 1987
4717817 1991
4735578 2001
4752234 2002
4757534 2005
4794242 1989
4799891 1995
4813879 1989
4814593 1998
4819202 1997
4819204 1989
4835524 2002
4841133 2002
4884710 1989
4899035 1990
4907290 1990
4932889 1994
4947531 1993
4976630 1992
4990759 1945
4992794 1991
5003314 1991
5006698 1991
5014312 1991

Here are a list of numbers, where the 1st row is C cell values and the
second row years is B cell Values. So can you help me find an efficient
way of counting 4735578 where it should look within the last 5 years
from 2001 and at the same time count the number of times it occurs in
A1:A200. The constraint is the some years are not there in the C1:C200
range.


--
Tomac
------------------------------------------------------------------------
Tomac's Profile: http://www.excelforum.com/member.php...o&userid=31555
View this thread: http://www.excelforum.com/showthread...hreadid=513917