+ Reply to Thread
Results 1 to 10 of 10

vlookup alternative to sum rows

  1. 07-05-2011, 04:52 AM #1
    emilyf
    emilyf is offline
    Registered User
    Join Date
    07-04-2011
    Location
    Chester, UK
    MS-Off Ver
    Excel 2007 Professional Plus
    Posts
    7

    vlookup alternative to sum rows

    Hi!
    I think this is a simple problem, but I've searched and I can't find the answer!
    I have a list of codes with quantities against them. Some of the codes appear 2 or 3 times with different quantities against them.
    On another worksheet I want to lookup against this list of codes and sum all quantities next to each occurence of that code. VLOOKUP doesn't work because it only returns the value of the first occurrence. I'm thinking maybe index match but am not sure how to use that.
    Can you help? I've attached a basic version of the worksheet...
    Thanks!
    Attached Files Attached Files
    Last edited by emilyf; 07-05-2011 at 07:11 AM.
    Reply With QuoteRegister To Reply
  2. 07-05-2011, 05:00 AM #2
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,509

    Re: vlookup alternative to sum rows

    F3: =SUMIF($A$3:$A$24,$E3,$B$3:$B$24)

    and copy down.


    Regards
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 07-05-2011, 05:09 AM #3
    emilyf
    emilyf is offline
    Registered User
    Join Date
    07-04-2011
    Location
    Chester, UK
    MS-Off Ver
    Excel 2007 Professional Plus
    Posts
    7

    Re: vlookup alternative to sum rows

    Perfect Thanks!! I knew it would be simple. Couldn't see the wood for the trees!
    Reply With QuoteRegister To Reply
  4. 07-05-2011, 05:19 AM #4
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,509

    Re: vlookup alternative to sum rows

    You're welcome. Like all these things, simple when you know, tough when you don't ;-)

    If this has answered your question, please mark the thread as solved. See my signature for details or the FAQ.

    Regards
    Reply With QuoteRegister To Reply
  5. 07-05-2011, 05:42 AM #5
    emilyf
    emilyf is offline
    Registered User
    Join Date
    07-04-2011
    Location
    Chester, UK
    MS-Off Ver
    Excel 2007 Professional Plus
    Posts
    7

    Re: vlookup alternative to sum rows

    I've hit a snag!!!
    The actual workbook is more complicated and links into other workbooks. I have managed to alter the formula you gave me accordinly, but it only seems to work when both workbooks are open. When I close the one it is reading from I get a #VALUE...
    :o(
    Reply With QuoteRegister To Reply
  6. 07-05-2011, 06:00 AM #6
    vipul.halbe
    vipul.halbe is offline
    Registered User
    Join Date
    06-29-2011
    Location
    MUMBAI
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    85

    Re: vlookup alternative to sum rows

    once the formula is extracted
    paste special the values but u wont be able to view the formulas
    Reply With QuoteRegister To Reply
  7. 07-05-2011, 06:03 AM #7
    emilyf
    emilyf is offline
    Registered User
    Join Date
    07-04-2011
    Location
    Chester, UK
    MS-Off Ver
    Excel 2007 Professional Plus
    Posts
    7

    Re: vlookup alternative to sum rows

    Thanks, unfortunately that won't work as the sheet it's reading from changes periodically so I need the values to update...
    Reply With QuoteRegister To Reply
  8. 07-05-2011, 06:08 AM #8
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,509

    Re: vlookup alternative to sum rows

    If you resorted to VBA, you could maybe convert the formulae to values when you close the source workbook and convert them to formulae when you open it.

    Not sure of the logistics of this but it would mean you have to enable macros.

    Regards
    Reply With QuoteRegister To Reply
  9. 07-05-2011, 06:46 AM #9
    emilyf
    emilyf is offline
    Registered User
    Join Date
    07-04-2011
    Location
    Chester, UK
    MS-Off Ver
    Excel 2007 Professional Plus
    Posts
    7

    Re: vlookup alternative to sum rows

    I Figured it!!
    The answer seems to be SUMPRODUCT, which does calculate from closed workbooks.
    Convert the formula above =SUMIF($A$3:$A$24,$E3,$B$3:$B$24) to:

    =SUMPRODUCT(--($A$3:$A$24=$E3),($B$3:$B$24))

    Works a treat! Thanks for the help and the nudge in the right direction!
    Reply With QuoteRegister To Reply
  10. 07-05-2011, 06:58 AM #10
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,509

    Re: vlookup alternative to sum rows

    I have to be honest and say I didn't consider SUMPRODUCT as you're using Excel 2007 and would therefore thought there should be a fuction using 2007 capabilities.

    Not to worry though, you have your solution.

    If your question has been answered, please mark your thread as solved. See my signature for details or the FAQ.

    Regards
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1