counting occurences from multiple ranges

[Tomac]

Hey All,

I have a problem with a code i am trying to use. This code,
SUMPRODUCT(--(ISNUMBER(FIND(C2,$A$2:$A$200,1))),--($B$2:$B$200=B2+5)),
does not help me in solving a problem i am trying to solve. This is the
problem. I have in A1:A100 twenty 6-digit numbers, and in B1:B200 is the
years. I am trying to find if my value in C1 matches the values in
A1:A100 range and the B1:B200 range. Let me give an example. C1=123456
and B1=1985. Now can you help me find a way to find the number of times
C1 occurs in the A1:A200 range and at the same time match only the Years
1985,1986, 1987,1988 and 1989 in B1:B200. *Remember the years 1988 and
1989 may not be in the range B1:B200.* I need to find an efficient way
to calculate for C1,C2,C3 and so on. Please help

 

[Guest]

Hi,
Referring to your previous postings and replies:

What is the relationship between contents of A1:A100 and B1:B200?

If we find 123456 occurs 100 times in A1:a100 how does this relate to a year
e.g 1985? How do they "match"?

 

[Tomac]

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.

 

[Guest]

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 (e-mail address removed)

=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.