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

In sheet2 C1; enter the below formula and copy down as required...

=SUMPRODUCT((Sheet1!$A$1:$D$1=A1)*Sheet1!$A$2:$D$1 00)

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


"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

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

Dear Jacob,

Based on the formula you have been given, is it possible on the $ D $ 100,
this number 100 can depend on the number on a cell. Example, when on Sheet2!
D2, there are number 4, then the formula that you provide will be a $ D $ 4?
There are additional suggestions?

Thanks,
Regard.


"Jacob Skaria" wrote:

In sheet2 C1; enter the below formula and copy down as required...

=SUMPRODUCT((Sheet1!$A$1:$D$1=A1)*Sheet1!$A$2:$D$1 00)

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


"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

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

=SUM(OFFSET(Sheet1!$A$1,1,MATCH(A1,Sheet1!$A$1:$C$ 1,)-1,B1,))


"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

Dear Jacob,

Thanks again, you're so clever.... its work!!!

Rgds,
Maulwy

"Jacob Skaria" wrote:

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

Dear Teethless mama,

Thanks, but your formula result still same as jacob's first answer. Again,
thanks a lot for your answer.

Rgds,
Maulwy

"Teethless mama" wrote:

=SUM(OFFSET(Sheet1!$A$1,1,MATCH(A1,Sheet1!$A$1:$C$ 1,)-1,B1,))


"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

Hi,

Try this

=SUMPRODUCT((Sheet1!$A$1:$C$1=Sheet2!$A1)*(Sheet1! $A$2:$C$4))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Maulwy" wrote in message
...
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

Dear Mr. Mathur,

This question has been answered well by Mr. Jacob Skaria. Thank you for your
answer.

"Ashish Mathur" wrote:

Hi,

Try this

=SUMPRODUCT((Sheet1!$A$1:$C$1=Sheet2!$A1)*(Sheet1! $A$2:$C$4))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Maulwy" wrote in message
...
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