Help me count this

[Tomac]

Ok let me give you all an example:

C B
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

Now i want to see how many times the value 4752234 occurs in the
A1:A200 range (which has 200 6 digit values. Each 6 digit values is
seperated by a space), and at the same time if those values are
occuring from the year 2001 to 2005. *Now the constraint is some C
values that occur in A1:A200 does not fall between 2001 and 2005 AND
some years are not available.* please remeber that i have values from
C1 to C200 which i have to calculate for.

 

[Don Guillett]

something like
=sumproduct((left(c2:c200=,6)="4752234")*1)

or

=sumproduct((left(c2:c200=,6)="4752234")*(b2:b200=2002))

 

[Bob Phillips]

=SUMPRODUCT(--(LEFT(A1:A100,7)="4752234"),--(MID(A1:A100,9,4)>="2001"),--(MI
D(A1:A100,9,4)<"2005"))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)