Does anyone have any suggestions on how to determine the occurrence for
following conditions?

There is another list of numbers under column A
3,7,6,5,4,9,5,6,1
There is a list of numbers under column B
1,2,6,8,9,10,12,15,18,21,26,28
There is a list of numbers under column C
1,2,3,4,5 ... 25,26,27

I would like to add each number under column A with all numbers under Column
B, and match with the number under column C, then sum all the occurrences and
return on column D
For example,
for the first number 3 in cell A1,
3+1=4, which
3+2=5
3+6=9
....
3+21=24
3+26=29
3+28=31

for the second number 7 in cell A2,
7+1=8, which
7+2=9
7+6=13
....
7+21=28
7+26=33
7+28=35

For the number 3 and 7 only at this stage,
The occurrence matching with the list of numbers under column C, is shown
below
1 0
2 0
3 0
4 1
5 1
6 0
7 0
8 1
9 2
10 0
11 1
12 1
13 2
14 0
15 2
16 1
17 1
18 1
19 1
20 0
21 1
22 1
23 0
24 1
25 1
26 0
27 0

I only show 2 steps on above example, but I would like to do it all numbers
under column A,
Does anyone have any suggestions on how to do it in Excel?
Thanks in advance for any suggestions
Eric

Eric wrote:
Does anyone have any suggestions on how to determine the occurrence for
following conditions?

There is another list of numbers under column A
3,7,6,5,4,9,5,6,1
There is a list of numbers under column B
1,2,6,8,9,10,12,15,18,21,26,28
There is a list of numbers under column C
1,2,3,4,5 ... 25,26,27

I would like to add each number under column A with all numbers under Column
B, and match with the number under column C, then sum all the occurrences and
return on column D
For example,
for the first number 3 in cell A1,
3+1=4, which
3+2=5
3+6=9
...
3+21=24
3+26=29
3+28=31

for the second number 7 in cell A2,
7+1=8, which
7+2=9
7+6=13
...
7+21=28
7+26=33
7+28=35

For the number 3 and 7 only at this stage,
The occurrence matching with the list of numbers under column C, is shown
below
1 0
2 0
3 0
4 1
5 1
6 0
7 0
8 1
9 2
10 0
11 1
12 1
13 2
14 0
15 2
16 1
17 1
18 1
19 1
20 0
21 1
22 1
23 0
24 1
25 1
26 0
27 0

I only show 2 steps on above example, but I would like to do it all numbers
under column A,
Does anyone have any suggestions on how to do it in Excel?
Thanks in advance for any suggestions
Eric

If you can use helper cells, this will do it.

In G1:
=INDEX($A$1:$A$9,1+COLUMN()-COLUMN($G:$G))+INDEX($B$1:$B$12,ROW(1:1))
fill through O12

Now in D1:
=COUNTIF($G$1:$O$12,C1)
fill down

Thank you very much for suggestions
Would it be possible to determine the occurrence without using the helper
cells?
Is there any built-in function within Excel? which can perform this kind of
complicated matching for occurrence.
Does anyone have any suggestions?
Thank anyone very much for suggestions
Eric

"smartin" wrote:

Eric wrote:
Does anyone have any suggestions on how to determine the occurrence for
following conditions?

There is another list of numbers under column A
3,7,6,5,4,9,5,6,1
There is a list of numbers under column B
1,2,6,8,9,10,12,15,18,21,26,28
There is a list of numbers under column C
1,2,3,4,5 ... 25,26,27

I would like to add each number under column A with all numbers under Column
B, and match with the number under column C, then sum all the occurrences and
return on column D
For example,
for the first number 3 in cell A1,
3+1=4, which
3+2=5
3+6=9
...
3+21=24
3+26=29
3+28=31

for the second number 7 in cell A2,
7+1=8, which
7+2=9
7+6=13
...
7+21=28
7+26=33
7+28=35

For the number 3 and 7 only at this stage,
The occurrence matching with the list of numbers under column C, is shown
below
1 0
2 0
3 0
4 1
5 1
6 0
7 0
8 1
9 2
10 0
11 1
12 1
13 2
14 0
15 2
16 1
17 1
18 1
19 1
20 0
21 1
22 1
23 0
24 1
25 1
26 0
27 0

I only show 2 steps on above example, but I would like to do it all numbers
under column A,
Does anyone have any suggestions on how to do it in Excel?
Thanks in advance for any suggestions
Eric

If you can use helper cells, this will do it.

In G1:
=INDEX($A$1:$A$9,1+COLUMN()-COLUMN($G:$G))+INDEX($B$1:$B$12,ROW(1:1))
fill through O12

Now in D1:
=COUNTIF($G$1:$O$12,C1)
fill down

If you don't want to use helper cells, try this formula in cell D1:

=SUM(--(TRANSPOSE(A$1:A$9)+B$1:B$12=C1))

Note: This is an array formula that has to be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER

Copy the formula down to D27.

Hope this helps / Lars-Åke

On Sat, 5 Sep 2009 21:35:01 -0700, Eric
wrote:

Thank you very much for suggestions
Would it be possible to determine the occurrence without using the helper
cells?
Is there any built-in function within Excel? which can perform this kind of
complicated matching for occurrence.
Does anyone have any suggestions?
Thank anyone very much for suggestions
Eric

"smartin" wrote:

Eric wrote:
Does anyone have any suggestions on how to determine the occurrence for
following conditions?

There is another list of numbers under column A
3,7,6,5,4,9,5,6,1
There is a list of numbers under column B
1,2,6,8,9,10,12,15,18,21,26,28
There is a list of numbers under column C
1,2,3,4,5 ... 25,26,27

I would like to add each number under column A with all numbers under Column
B, and match with the number under column C, then sum all the occurrences and
return on column D
For example,
for the first number 3 in cell A1,
3+1=4, which
3+2=5
3+6=9
...
3+21=24
3+26=29
3+28=31

for the second number 7 in cell A2,
7+1=8, which
7+2=9
7+6=13
...
7+21=28
7+26=33
7+28=35

For the number 3 and 7 only at this stage,
The occurrence matching with the list of numbers under column C, is shown
below
1 0
2 0
3 0
4 1
5 1
6 0
7 0
8 1
9 2
10 0
11 1
12 1
13 2
14 0
15 2
16 1
17 1
18 1
19 1
20 0
21 1
22 1
23 0
24 1
25 1
26 0
27 0

I only show 2 steps on above example, but I would like to do it all numbers
under column A,
Does anyone have any suggestions on how to do it in Excel?
Thanks in advance for any suggestions
Eric

If you can use helper cells, this will do it.

In G1:
=INDEX($A$1:$A$9,1+COLUMN()-COLUMN($G:$G))+INDEX($B$1:$B$12,ROW(1:1))
fill through O12

Now in D1:
=COUNTIF($G$1:$O$12,C1)
fill down

Thank you very very much for solving this tough question
Could you please tell me how to interpret this structure on using TRANSPOSE
function?
Thank everyone very much for suggestions
Eric

"Lars-Ã…ke Aspelin" wrote:


If you don't want to use helper cells, try this formula in cell D1:

=SUM(--(TRANSPOSE(A$1:A$9)+B$1:B$12=C1))

Note: This is an array formula that has to be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER

Copy the formula down to D27.

Hope this helps / Lars-Ã…ke

On Sat, 5 Sep 2009 21:35:01 -0700, Eric
wrote:

Thank you very much for suggestions
Would it be possible to determine the occurrence without using the helper
cells?
Is there any built-in function within Excel? which can perform this kind of
complicated matching for occurrence.
Does anyone have any suggestions?
Thank anyone very much for suggestions
Eric

"smartin" wrote:

Eric wrote:
Does anyone have any suggestions on how to determine the occurrence for
following conditions?

There is another list of numbers under column A
3,7,6,5,4,9,5,6,1
There is a list of numbers under column B
1,2,6,8,9,10,12,15,18,21,26,28
There is a list of numbers under column C
1,2,3,4,5 ... 25,26,27

I would like to add each number under column A with all numbers under Column
B, and match with the number under column C, then sum all the occurrences and
return on column D
For example,
for the first number 3 in cell A1,
3+1=4, which
3+2=5
3+6=9
...
3+21=24
3+26=29
3+28=31

for the second number 7 in cell A2,
7+1=8, which
7+2=9
7+6=13
...
7+21=28
7+26=33
7+28=35

For the number 3 and 7 only at this stage,
The occurrence matching with the list of numbers under column C, is shown
below
1 0
2 0
3 0
4 1
5 1
6 0
7 0
8 1
9 2
10 0
11 1
12 1
13 2
14 0
15 2
16 1
17 1
18 1
19 1
20 0
21 1
22 1
23 0
24 1
25 1
26 0
27 0

I only show 2 steps on above example, but I would like to do it all numbers
under column A,
Does anyone have any suggestions on how to do it in Excel?
Thanks in advance for any suggestions
Eric

If you can use helper cells, this will do it.

In G1:
=INDEX($A$1:$A$9,1+COLUMN()-COLUMN($G:$G))+INDEX($B$1:$B$12,ROW(1:1))
fill through O12

Now in D1:
=COUNTIF($G$1:$O$12,C1)
fill down

TRANSPOSE(A$1:A$9) converts the 9 element column vector
{A1;A2;A3;A4;A5;A6;A7;A8;A9} to a 9 element row vector with the same
elements {A1,A2,A3,A4,A5,A6,A7,A8,A9}

When a row vector and a column vector are added, using an array
formula, the result is a matrix with the same number of rows as the
row vector and the same number of columns as the column vector.
(This is the same matrix that you have in the cells G1 to O12 in the
helper cells solution).
Each element of the matrix is compared to the value in C1 and the
result is a new matrix, with the same dimensions, with boolean values
TRUE or FALSE depending on the result of the comparision.
The boolean values are transformed to numeric values by the double
negation operator, - -.
(It is also possible to add with 0, 0+, or multiply with 1, 1*).
Finally all the numeric values are summed with SUM.

Hope this makes sence / Lars-Åke


On Sat, 5 Sep 2009 23:13:01 -0700, Eric
wrote:

Thank you very very much for solving this tough question
Could you please tell me how to interpret this structure on using TRANSPOSE
function?
Thank everyone very much for suggestions
Eric

"Lars-Åke Aspelin" wrote:


If you don't want to use helper cells, try this formula in cell D1:

=SUM(--(TRANSPOSE(A$1:A$9)+B$1:B$12=C1))

Note: This is an array formula that has to be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER

Copy the formula down to D27.

Hope this helps / Lars-Åke

On Sat, 5 Sep 2009 21:35:01 -0700, Eric
wrote:

Thank you very much for suggestions
Would it be possible to determine the occurrence without using the helper
cells?
Is there any built-in function within Excel? which can perform this kind of
complicated matching for occurrence.
Does anyone have any suggestions?
Thank anyone very much for suggestions
Eric

"smartin" wrote:

Eric wrote:
Does anyone have any suggestions on how to determine the occurrence for
following conditions?

There is another list of numbers under column A
3,7,6,5,4,9,5,6,1
There is a list of numbers under column B
1,2,6,8,9,10,12,15,18,21,26,28
There is a list of numbers under column C
1,2,3,4,5 ... 25,26,27

I would like to add each number under column A with all numbers under Column
B, and match with the number under column C, then sum all the occurrences and
return on column D
For example,
for the first number 3 in cell A1,
3+1=4, which
3+2=5
3+6=9
...
3+21=24
3+26=29
3+28=31

for the second number 7 in cell A2,
7+1=8, which
7+2=9
7+6=13
...
7+21=28
7+26=33
7+28=35

For the number 3 and 7 only at this stage,
The occurrence matching with the list of numbers under column C, is shown
below
1 0
2 0
3 0
4 1
5 1
6 0
7 0
8 1
9 2
10 0
11 1
12 1
13 2
14 0
15 2
16 1
17 1
18 1
19 1
20 0
21 1
22 1
23 0
24 1
25 1
26 0
27 0

I only show 2 steps on above example, but I would like to do it all numbers
under column A,
Does anyone have any suggestions on how to do it in Excel?
Thanks in advance for any suggestions
Eric

If you can use helper cells, this will do it.

In G1:
=INDEX($A$1:$A$9,1+COLUMN()-COLUMN($G:$G))+INDEX($B$1:$B$12,ROW(1:1))
fill through O12

Now in D1:
=COUNTIF($G$1:$O$12,C1)
fill down

=SUM(--(TRANSPOSE(A$1:A$9)+B$1:B$12=C1))

I would like to change A$1:!A$9 into a variable using indirect function,
=SUM(--(TRANSPOSE(INDIRECT("B$595:B$"&594+B$594))+A$2:A$9 0=H2))

but it returns #VALUE, do you have any suggestions what wrong this statement
is on using indirect function?
Thanks everyone very much for any suggestions
Eric

Note: This is an array formula that has to be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER


"Lars-Ã…ke Aspelin" wrote:


If you don't want to use helper cells, try this formula in cell D1:

=SUM(--(TRANSPOSE(A$1:A$9)+B$1:B$12=C1))

Note: This is an array formula that has to be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER

Copy the formula down to D27.

Hope this helps / Lars-Ã…ke

On Sat, 5 Sep 2009 21:35:01 -0700, Eric
wrote:

Thank you very much for suggestions
Would it be possible to determine the occurrence without using the helper
cells?
Is there any built-in function within Excel? which can perform this kind of
complicated matching for occurrence.
Does anyone have any suggestions?
Thank anyone very much for suggestions
Eric

"smartin" wrote:

Eric wrote:
Does anyone have any suggestions on how to determine the occurrence for
following conditions?

There is another list of numbers under column A
3,7,6,5,4,9,5,6,1
There is a list of numbers under column B
1,2,6,8,9,10,12,15,18,21,26,28
There is a list of numbers under column C
1,2,3,4,5 ... 25,26,27

I would like to add each number under column A with all numbers under Column
B, and match with the number under column C, then sum all the occurrences and
return on column D
For example,
for the first number 3 in cell A1,
3+1=4, which
3+2=5
3+6=9
...
3+21=24
3+26=29
3+28=31

for the second number 7 in cell A2,
7+1=8, which
7+2=9
7+6=13
...
7+21=28
7+26=33
7+28=35

For the number 3 and 7 only at this stage,
The occurrence matching with the list of numbers under column C, is shown
below
1 0
2 0
3 0
4 1
5 1
6 0
7 0
8 1
9 2
10 0
11 1
12 1
13 2
14 0
15 2
16 1
17 1
18 1
19 1
20 0
21 1
22 1
23 0
24 1
25 1
26 0
27 0

I only show 2 steps on above example, but I would like to do it all numbers
under column A,
Does anyone have any suggestions on how to do it in Excel?
Thanks in advance for any suggestions
Eric

If you can use helper cells, this will do it.

In G1:
=INDEX($A$1:$A$9,1+COLUMN()-COLUMN($G:$G))+INDEX($B$1:$B$12,ROW(1:1))
fill through O12

Now in D1:
=COUNTIF($G$1:$O$12,C1)
fill down

Make sure that you use CTRL+SHIFT+ENTER.
If you use only ENTER you will get the #VALUE! error.
When you enter the formula correctly you will get the curly brackets,
{ } around the formula. These are added by Excel, you should not write
them yourself.

Lars-Åke

On Sat, 5 Sep 2009 23:56:01 -0700, Eric
wrote:

=SUM(--(TRANSPOSE(A$1:A$9)+B$1:B$12=C1))

I would like to change A$1:!A$9 into a variable using indirect function,
=SUM(--(TRANSPOSE(INDIRECT("B$595:B$"&594+B$594))+A$2:A$9 0=H2))

but it returns #VALUE, do you have any suggestions what wrong this statement
is on using indirect function?
Thanks everyone very much for any suggestions
Eric

Note: This is an array formula that has to be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER


"Lars-Åke Aspelin" wrote:


If you don't want to use helper cells, try this formula in cell D1:

=SUM(--(TRANSPOSE(A$1:A$9)+B$1:B$12=C1))

Note: This is an array formula that has to be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER

Copy the formula down to D27.

Hope this helps / Lars-Åke

On Sat, 5 Sep 2009 21:35:01 -0700, Eric
wrote:

Thank you very much for suggestions
Would it be possible to determine the occurrence without using the helper
cells?
Is there any built-in function within Excel? which can perform this kind of
complicated matching for occurrence.
Does anyone have any suggestions?
Thank anyone very much for suggestions
Eric

"smartin" wrote:

Eric wrote:
Does anyone have any suggestions on how to determine the occurrence for
following conditions?

There is another list of numbers under column A
3,7,6,5,4,9,5,6,1
There is a list of numbers under column B
1,2,6,8,9,10,12,15,18,21,26,28
There is a list of numbers under column C
1,2,3,4,5 ... 25,26,27

I would like to add each number under column A with all numbers under Column
B, and match with the number under column C, then sum all the occurrences and
return on column D
For example,
for the first number 3 in cell A1,
3+1=4, which
3+2=5
3+6=9
...
3+21=24
3+26=29
3+28=31

for the second number 7 in cell A2,
7+1=8, which
7+2=9
7+6=13
...
7+21=28
7+26=33
7+28=35

For the number 3 and 7 only at this stage,
The occurrence matching with the list of numbers under column C, is shown
below
1 0
2 0
3 0
4 1
5 1
6 0
7 0
8 1
9 2
10 0
11 1
12 1
13 2
14 0
15 2
16 1
17 1
18 1
19 1
20 0
21 1
22 1
23 0
24 1
25 1
26 0
27 0

I only show 2 steps on above example, but I would like to do it all numbers
under column A,
Does anyone have any suggestions on how to do it in Excel?
Thanks in advance for any suggestions
Eric

If you can use helper cells, this will do it.

In G1:
=INDEX($A$1:$A$9,1+COLUMN()-COLUMN($G:$G))+INDEX($B$1:$B$12,ROW(1:1))
fill through O12

Now in D1:
=COUNTIF($G$1:$O$12,C1)
fill down