How to write sum if formula if criteria is a variable parameter?

[Iris14]

<colgroup><col><col span="3"></colgroup><tbody></tbody>

I have a Table 1 which contained the sub account number and the related amount. The sub account numbers are then grouped to a master account number which is presented in Table 2. Table 2 is like a directory to guide what sub account numbers need to be added together.

Table 1
Sub Account number	Amount
SDFG	1
D	2
FG	3
D	6
GH	8
T	854
RH	565
H	45
GH	546
GH	54
J	5
GH	2

<colgroup><col><col></colgroup><tbody>
</tbody>

Table 2
Master account 1	GH	J	T

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

I would like to do a sum if formula to sum the amount in table 1 if column A of Table 1 (the sub account number) matches any of the account number in row 1 of Table 2.

The number of sub account number in the Master account is variable.

I have tried to build array formula like {=SUM(IF(A:A={"GH","J","T"},B:B,0))}
But then the account number would need to be keyed in manually and there are a lot of sub account numbers that needed to be built into the formula.

I would like to ask if there is anyway to build formula, e.g. match, address, lookup, index and etc to do the work (adding the amount in table 1 of any sub account numbers that belong to the same master account).

Thank you very much for any help!!!

 

[vlady]

try something like this

=SUM(IF(ISERROR(MATCH(A:A,C1:F1,0))=FALSE,B:B,0))
C1:F1 consist of those {"GH","J","T"} letters

 

[Michael M]

couple of other options

Excel 2007
	A	B	C	D	E	F
1	Sub Account number	Amount		GH	J	T
2	SDFG	1
3	D	2
4	FG	3
5	D	6
6	GH	8
7	T	854
8	RH	565
9	H	45
10	GH	546
11	GH	54
12	J	5
13	GH	2
14				1469
15				1469
Sheet1

Cell Formulas
Range	Formula
D14	=SUMIF(A2:A13,D1,B2:B13)+SUMIF(A2:A13,E1,B2:B13)+SUMIF(A2:A13,F1,B2:B13)
D15	=SUMPRODUCT(--(A2:A13=D1)+(A2:A13=E1)+(A2:A13=F1),B2:B13)

 

[Scott Huish]

Assuming your values are in Row 1 of Table2:

=SUMPRODUCT((A2:A12=Table2!1:1)*B2:B12)

 

[Iris14]

Thanks a lot vlady for your prompt reply !!! It works !!! Thanks a lot for your great help !!! It is very much appreciated.

 

[Iris14]

Thank you all for the prompt reply !!! It is a great forum. I definitely find the right spot !