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