Try the below..
=SUMPRODUCT((Sheet1!$A$1:$D$1=A1)*INDIRECT("Sheet1 !$A$2:$D$" & B1+1))

If this post helps click Yes
---------------
Jacob Skaria


"Maulwy" wrote:

Dear Jacob,

Thank you for your quick answer. I am sorry, actually I want the data as
follows:
Sheet1:
A B C
1 AAA BBB CCC
2 1 4 7
3 2 5 8
4 3 6 9
5 4 7 10
6 5 8 11
7 6 9 12
8 7 10 13
9 8 11 14
10 9 12 15

Sheet2:
A B C
1 CCC 3 25
2 BBB 5 30

where, C1 is the sum of Sheet1! C2: C4, adds depth of 3 cells, as well as
C2, is the sum of Sheet1! B2: B6, adds depth of 5 cells, according to the
number listed in column "B". This is an extension of the formula and the
conditions that yesterday I asked.

Thanks before,
Rgds,
Maulwy

"Maulwy" wrote:

If I had data:
Sheet1:
A B C
1 AAA BBB CCC
2 1 4 7
3 2 5 8
4 3 6 9

Sheet2:
A B
1 CCC 24
2 BBB 15
3 AAA 6


Where B2 is the sum of Sheet1: C2-C4, after HLOOKUP function in cell A1, as
well as B3 and B4 cells, do HLOOKUP sum based on the results.
Is there a formula that can meet these conditions?
Thank you for the information.

Regards,
Maulwy