SUM formula after HLOOKUP result true

[Maulwy]

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

 

[Jacob Skaria]

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

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

If this post helps click Yes

 

[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]

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]

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

If this post helps click Yes

 

[Teethless mama]

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

 

[Maulwy]

Dear Jacob,

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

Rgds,
Maulwy

 

[Maulwy]

Dear Teethless mama,

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

Rgds,
Maulwy

 

[Ashish Mathur]

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]

Dear Mr. Mathur,

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