Match Index multiple criteria

[damondole]

Trying to get the result in C8 and D8 to read 480 and 480 respectively using the following formula:

=IF(OR(AND(G1=A8,B1="BAN"),AND(G2=A8,B2="BAN"),AND(G3=A8,B3="BAN"),AND(G4=A8,B4="BAN")),INDEX(C1:C4,MATCH(A8,G1:G4,0)),"")

The result does not sum both 240 listed in C1&C2, C3&C4 respectively.. Need help

[TABLE="width: 700"]
<!--StartFragment--> <colgroup><col width="100" span="7" style="width:75pt"> </colgroup><tbody>[TR]
[TD="width: 100, bgcolor: #4472C4"]Column1[/TD]
[TD="width: 100, bgcolor: #4472C4"]Column2[/TD]
[TD="width: 100, bgcolor: #4472C4"]Column3[/TD]
[TD="width: 100, bgcolor: #4472C4"]Column4[/TD]
[TD="width: 100, bgcolor: #4472C4"]Column5[/TD]
[TD="width: 100, bgcolor: #4472C4"]Column6[/TD]
[TD="width: 100, bgcolor: #4472C4"]Column7[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "]LPO53D[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "]BAN[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"]240[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"]10[/TD]
[/TR]
[TR]
[TD]LPO53D[/TD]
[TD]BAN[/TD]
[TD="align: right"]240[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "]EPO53C[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "]ORG[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"]240[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"]10[/TD]
[/TR]
[TR]
[TD]EPO53C[/TD]
[TD]ORG[/TD]
[TD="align: right"]240[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD="align: right"]240[/TD]
[TD="align: right"]240[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]

<attachment></attachment>

 

[jtakw]

Hi,

If you have SUMIFS:

Excel 2010
	A	B	C	D	E	F	G
1	LPO53D	BAN	240				10
2	LPO53D	BAN	240				10
3	EPO53C	ORG	240				10
4	EPO53C	ORG	240				10
5
6
7
8	10		480	480
Sheet3

Cell Formulas
Range	Formula
C8	=SUMIFS($C1:$C4,$G1:$G4,$A8,$B1:$B4,"BAN")
D8	=SUMIFS($C1:$C4,$G1:$G4,$A8,$B1:$B4,"ORG")

 

[steve the fish]

Hi. Do you mean this?

=SUMIFS(C1:C4,B1:B4,"BAN",G1:G4,A8)

 

[damondole]

Hi. Do you mean this?

=SUMIFS(C1:C4,B1:B4,"BAN",G1:G4,A8)

Thank you.. That worked!!!