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

[Iris14]

[TABLE="width: 354"]
<colgroup><col><col span="3"></colgroup><tbody></tbody>[/TABLE]
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="width: 226"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Table 1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Sub Account number[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]SDFG[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]FG[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]GH[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD="align: right"]854[/TD]
[/TR]
[TR]
[TD]RH[/TD]
[TD="align: right"]565[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD]GH[/TD]
[TD="align: right"]546[/TD]
[/TR]
[TR]
[TD]GH[/TD]
[TD="align: right"]54[/TD]
[/TR]
[TR]
[TD]J[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]GH[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 354"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]Table 2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Master account 1[/TD]
[TD]GH[/TD]
[TD]J[/TD]
[TD]T[/TD]
[/TR]
</tbody>[/TABLE]


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 !