How to determine the occurrence?

[Eric]

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]

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

 

[Lars-Åke Aspelin]

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

 

[Eric]

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

 

[Eric]

=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$90=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]

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

 

[Lars-Åke Aspelin]

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

 

[Lars-Åke Aspelin]

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

 

[Eric]

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]

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