how to do SUMIFS on multiple filtered columns
Results 1 to 2 of 2

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

  1. #1
    New Member
    Join Date
    Mar 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,729
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: how to do SUMIFS on multiple filtered columns

    Maybe these formulas will help you


    Hoja3

     ABCDEFGHIJ
    1IDSalaryLevel1Level2 IDJohnTomRichardSwetha
    2ID1236JohnTom ID12320121416
    3ID1236JohnTom      
    4ID1237JohnRichard      
    5ID1237JohnRichard      
    6ID1238JohnSwetha      
    7ID1238JohnSwetha      
    8ID12310JohnRandy      
    9ID12310JohnRandy      
    10ID12310RamRocky      

    Formulas
    CellFormula
    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
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •