how to do SUMIFS on multiple filtered columns

Kumarxl

New Member
Joined
Mar 10, 2019
Messages
4
Hi ,I want to SUM after applying filters on multiple columns

Table 1
IDSalaryLevel1Level2
ID1236JohnTom
ID1236JohnTom
ID1237JohnRichard
ID1237JohnRichard
ID1238JohnSwetha
ID1238JohnSwetha
ID12310JohnRandy
ID12310JohnRandy
ID12310RamRocky

<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)
IDJohnTomRichardSwetha
ID12320121416

<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)
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Maybe these formulas will help you


Excel Workbook
ABCDEFGHIJ
1IDSalaryLevel1Level2IDJohnTomRichardSwetha
2ID1236JohnTomID12320121416
3ID1236JohnTom
4ID1237JohnRichard
5ID1237JohnRichard
6ID1238JohnSwetha
7ID1238JohnSwetha
8ID12310JohnRandy
9ID12310JohnRandy
10ID12310RamRocky
Hoja3
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top