how to do SUMIFS on multiple filtered columns

[Kumarxl]

Hi ,I want to SUM after applying filters on multiple columns

Table 1[TABLE="width: 370"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Salary[/TD]
[TD]Level1[/TD]
[TD]Level2[/TD]
[/TR]
[TR]
[TD]ID123[/TD]
[TD]6[/TD]
[TD]John[/TD]
[TD]Tom[/TD]
[/TR]
[TR]
[TD]ID123[/TD]
[TD]6[/TD]
[TD]John[/TD]
[TD]Tom[/TD]
[/TR]
[TR]
[TD]ID123[/TD]
[TD]7[/TD]
[TD]John[/TD]
[TD]Richard[/TD]
[/TR]
[TR]
[TD]ID123[/TD]
[TD]7[/TD]
[TD]John[/TD]
[TD]Richard[/TD]
[/TR]
[TR]
[TD]ID123[/TD]
[TD]8[/TD]
[TD]John[/TD]
[TD]Swetha[/TD]
[/TR]
[TR]
[TD]ID123[/TD]
[TD]8[/TD]
[TD]John[/TD]
[TD]Swetha[/TD]
[/TR]
[TR]
[TD]ID123[/TD]
[TD]10[/TD]
[TD]John[/TD]
[TD]Randy[/TD]
[/TR]
[TR]
[TD]ID123[/TD]
[TD]10[/TD]
[TD]John[/TD]
[TD]Randy[/TD]
[/TR]
[TR]
[TD]ID123[/TD]
[TD]10[/TD]
[TD]Ram[/TD]
[TD]Rocky[/TD]
[/TR]
</tbody>[/TABLE]

From the table1, I want to SUM - after applying filters on two columns (Level1 and Level2) and fill another table2 on matching column header.
LIST - John,Tom,Richard,Swetha

For Instance , I need to filter John on Level1 column(after excluding other names from the list on Level 2 column) and populate the sum in table 2.
John column should be filled with sum from row 8,9(table1). 10+10 = 20 and exclude row 2 to 7 (other names from the list)

Similarly ,Tom column should be filled with SUM only from row 2,3(table1). 6+ 6 =12
Richard column should be filled with SUM only from row 4,5(table1). 7+ 7 =14
Swetha column should be filled with SUM only from row 6,7(table1). 8+ 8 =16
Row 10 should not be considered as there is no matching name from the list


Table 2 (Names from the list will be the column headers)
[TABLE="width: 380"]
<tbody>[TR]
[TD]ID[/TD]
[TD]John[/TD]
[TD]Tom[/TD]
[TD]Richard[/TD]
[TD]Swetha[/TD]
[/TR]
[TR]
[TD]ID123[/TD]
[TD]20[/TD]
[TD]12[/TD]
[TD]14[/TD]
[TD="align: right"]16[/TD]
[/TR]
</tbody>[/TABLE]

Assumption: Level 1 and level 2 columns are mutually exclusive

I started with SUMIFS,however i am stuck on how to exclude 2nd column.can you please help with solution or approach .

=SUMIFS(Table1!B:B,Table2!A:A,Table1!A:A,Table1!D:D,Table2!B1:E1,Table2!B1)

 

[DanteAmor]

Maybe these formulas will help you

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	ID	Salary	Level1	Level2		ID	John	Tom	Richard	Swetha
2	ID123	6	John	Tom		ID123	20	12	14	16
3	ID123	6	John	Tom
4	ID123	7	John	Richard
5	ID123	7	John	Richard
6	ID123	8	John	Swetha
7	ID123	8	John	Swetha
8	ID123	10	John	Randy
9	ID123	10	John	Randy
10	ID123	10	Ram	Rocky
Hoja3