# Thread: how to do SUMIFS on multiple filtered columns Thanks: 0 Likes: 0

1. ## how to do SUMIFS on multiple filtered columns

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

Table 1
 ID Salary Level1 Level2 ID123 6 John Tom ID123 6 John Tom ID123 7 John Richard ID123 7 John Richard ID123 8 John Swetha ID123 8 John Swetha ID123 10 John Randy ID123 10 John Randy ID123 10 Ram Rocky

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)
 ID John Tom Richard Swetha ID123 20 12 14 16

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)

2. ## Re: how to do SUMIFS on multiple filtered columns

Hoja3

 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

Formulas
 Cell Formula G2 =SUMIFS(\$B\$2:\$B\$10,\$A\$2:\$A\$10,\$F2,\$C\$2:\$C\$10,G\$1)-SUM(H2:J2) H2 =SUMIFS(\$B\$2:\$B\$10,\$A\$2:\$A\$10,\$F2,\$D\$2:\$D\$10,H\$1) I2 =SUMIFS(\$B\$2:\$B\$10,\$A\$2:\$A\$10,\$F2,\$D\$2:\$D\$10,I\$1) J2 =SUMIFS(\$B\$2:\$B\$10,\$A\$2:\$A\$10,\$F2,\$D\$2:\$D\$10,J\$1)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8