Hi ,I want to SUM after applying filters on multiple columns
Table 1
<tbody>
</tbody>
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)
<tbody>
</tbody>
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)
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 |
<tbody>
</tbody>
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 |
<tbody>
</tbody>
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)