Results 1 to 5 of 5

Thread: Help with a Vlookup and SUMIF

  1. #1
    Junior Member
    Join Date
    Apr 2012
    Posts
    8
    Rep Power
    0

    Help with a Vlookup and SUMIF

    Hi,
    I need help with a formula that looks for a name in the "Name" column (which contains the same name multiple times) and then adds its value in the column "Expense Program" and column "Expense Admin".

    Please see attachment for detail info.

    Thank you very much!!!

    Formula Help.xlsx

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Lucero View Post
    Hi,
    I need help with a formula that looks for a name in the "Name" column (which contains the same name multiple times) and then adds its value in the column "Expense Program" and column "Expense Admin".
    Does this formula do what you want...

    =SUMIF(A$2:A$42,F2,B$2:B$42)+SUMIF(A$2:A$42,F2,C$2 :C$42)

  3. #3
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Rick Rothstein View Post
    Does this formula do what you want...

    =SUMIF(A$2:A$42,F2,B$2:B$42)+SUMIF(A$2:A$42,F2,C$2 :C$42)
    Here is another formula that I think works the way you want...

    =SUMPRODUCT((A$2:A$42=F9)*B$2:C$42)

  4. #4
    Junior Member
    Join Date
    Apr 2012
    Posts
    8
    Rep Power
    0
    Both of them worked! Thank you!!

    oh and how can I add an additional criteria to that formula? I added it in columns D & E "S/NS" and "SUP"
    I manually added A & B so that you can see the results needed.

    Thank you!
    Formula Help2.xlsx

  5. #5
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Lucero View Post
    Both of them worked! Thank you!!

    oh and how can I add an additional criteria to that formula? I added it in columns D & E "S/NS" and "SUP"
    I manually added A & B so that you can see the results needed.
    Use these formulas in the indicated cells and then copy them down...

    Code:
    H2:  =SUMPRODUCT((A$2:A$42=G3)*(D$2:D$42="NS")*(E$2:E$42="Y")*B$2:C$42)
    
    I2:  =SUMPRODUCT((A$2:A$42=G3)*(D$2:D$42="NS")*(E$2:E$42="N")*B$2:C$42)
    
    J2:  =SUMPRODUCT((A$2:A$42=G3)*(D$2:D$42="S")*(E$2:E$42="Y")*B$2:C$42)
    
    K2:  =SUMPRODUCT((A$2:A$42=G3)*(D$2:D$42="S")*(E$2:E$42="N")*B$2:C$42)
    Notice this is the same formula repeated with the "NS" and "Y" from the first formul changed to match the two conditions indicated in the headers (H2:K2).
    Last edited by Rick Rothstein; 07-24-2012 at 05:06 AM.

Similar Threads

  1. Replies: 7
    Last Post: 05-15-2013, 02:56 PM
  2. Replies: 7
    Last Post: 05-15-2013, 07:55 AM
  3. Replies: 5
    Last Post: 04-07-2013, 05:11 PM
  4. Replies: 4
    Last Post: 08-11-2012, 10:50 PM
  5. Update data from closed workbook with sumif formula
    By leopaulc in forum Excel Help
    Replies: 4
    Last Post: 11-02-2011, 02:58 AM

Posting Permissions

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