ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   How to determine the occurrence? (https://www.excelbanter.com/excel-discussion-misc-queries/241783-how-determine-occurrence.html)

Eric
How to determine the occurrence?
 
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

smartin
How to determine the occurrence?
 
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

Eric
How to determine the occurrence?
 
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

Lars-Åke Aspelin[_2_]
How to determine the occurrence?
 

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

Eric
How to determine the occurrence?
 
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

Eric
How to determine the occurrence?
 
=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

Lars-Åke Aspelin[_2_]
How to determine the occurrence?
 
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

Lars-Åke Aspelin[_2_]
How to determine the occurrence?
 
Also make sure that you ranges, B1:B12 and B595:B??? only contain
numbers. Cells with text, even a single space character, will lead to
the #VALUE! error.

Lars-Åke

On Sun, 06 Sep 2009 17:31:25 GMT, Lars-Åke Aspelin
wrote:

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

Lars-Åke Aspelin[_2_]
How to determine the occurrence?
 
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

Eric
How to determine the occurrence?
 
Thank everyone very much for suggestions
=SUM(--(TRANSPOSE(A$1:A$9)+B$1:B$12=C1))
When I add the list of numbers B$1:B$12, can I add it with workday function?
=SUM(--(WORKDAY(TRANSPOSE(A$1:A$9),B$1:B$12)=C1)),
but when I add workday function into statement, it returns #VALUE!.
Do you have any suggestions?
Thank you very much for any suggestions
Eric

"Lars-Ã…ke Aspelin" wrote:

Also make sure that you ranges, B1:B12 and B595:B??? only contain
numbers. Cells with text, even a single space character, will lead to
the #VALUE! error.

Lars-Ã…ke

On Sun, 06 Sep 2009 17:31:25 GMT, Lars-Ã…ke Aspelin
wrote:

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

Lars-Åke Aspelin[_2_]
How to determine the occurrence?
 
I don't think that the WORKDAY() function is possible to use in the
array formula context. Back to the formula with helper cells proposed
by smartin.

In cell G1, and fill through O12:

=WORKDAY(INDEX($A$1:$A$9,1+COLUMN()-COLUMN($G:$G)),INDEX($B$1:$B$12,ROW(1:1)))

Maybe someone else can find a solution including the WORKDAY()
function without the use of helper cells.

There is always the possibility to have a macro to fill in the results
in column D. Try this:

Sub test()
With ActiveSheet
For c = 1 To 27
s = 0
For a = 1 To 9
For b = 1 To 12
If Application.WorksheetFunction.WorkDay(.Cells(a, "A"),
..Cells(b, "B")) = .Cells(c, "C") Then
s = s + 1
End If
Next b
Next a
.Cells(c, "D") = s
Next c
End With
End Sub

Hope this helps / Lars-Åke


On Sun, 6 Sep 2009 17:12:01 -0700, Eric
wrote:

Thank everyone very much for suggestions
=SUM(--(TRANSPOSE(A$1:A$9)+B$1:B$12=C1))
When I add the list of numbers B$1:B$12, can I add it with workday function?
=SUM(--(WORKDAY(TRANSPOSE(A$1:A$9),B$1:B$12)=C1)),
but when I add workday function into statement, it returns #VALUE!.
Do you have any suggestions?
Thank you very much for any suggestions
Eric

"Lars-Åke Aspelin" wrote:

Also make sure that you ranges, B1:B12 and B595:B??? only contain
numbers. Cells with text, even a single space character, will lead to
the #VALUE! error.

Lars-Åke

On Sun, 06 Sep 2009 17:31:25 GMT, Lars-Åke Aspelin
wrote:

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


All times are GMT +1. The time now is 09:04 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com