+ Reply to Thread
Results 1 to 6 of 6

Using Vlookup to find several items and then sum these items

  1. #1
    Registered User
    Join Date
    12-04-2010
    Location
    Jamaica
    MS-Off Ver
    Excel 2003
    Posts
    92

    Using Vlookup to find several items and then sum these items

    Hello Everyone, Can someone help me with this.

    Lets say in column "A" i have item numbers for products that i sell, and in column "C" and "D" i have the amount sold for each product. One of these products named "Big Deal" has four (4) item numbers, the item numbers are (101,155,165 & 200) I now need a formula that will look for these item numbers in colum "A" and then sum or total the amount sold for this item.

    I tried this formula but its not working: Sumif(A1:D7,(vlookup{101,155,165,200},A1:D7,{3,4},0)),(A1:A7))

    Column Column Column Column
    A B C D
    Row# Item# Product Sold Sold
    1. 101 Big Deal 15 20
    2. 107 Star Meal 9 10
    3. 112 Hot Wings 22 50
    4. 150 Popcorn 33 75
    5. 155 Big Deal 70 65
    6. 165 Big Deal 90 80
    7. 200 Big Deal 20 12

    Sumif(A1:D7,(vlookup{101,155,165,200},A1:D7,{3,4},0)),(A1:A7))
    Last edited by headley4ever; 12-04-2010 at 02:50 PM. Reason: It is jumbled

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Using Vlookup to find several items and then sum these items

    In some cell (i.e. E1) write what you looking for (Big Deal).

    Then use this formula:

    =SUMIF(B1:B7, E1, C1:C7)+SUMIF(B1:B7, E1, D1:D7)

  3. #3
    Registered User
    Join Date
    12-04-2010
    Location
    Jamaica
    MS-Off Ver
    Excel 2003
    Posts
    92

    Re: Using Vlookup to find several items and then sum these items

    Thanks for your suggestion however can this be done using vlookup?

  4. #4
    Registered User
    Join Date
    12-04-2010
    Location
    Jamaica
    MS-Off Ver
    Excel 2003
    Posts
    92

    Re: Using Vlookup to find several items and then sum these items

    i really need a formula that will look in column "A' for these 4 item numbers and then sum the amount sold which is in colum "C" & "D"

  5. #5
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Using Vlookup to find several items and then sum these items

    doesn't zbor's suggestion do just that?
    this will work with the numbers
    =SUMPRODUCT(($B$2:$B$8=101)+($B$2:$B$8=155)+($B$2:$B$8=165)+($B$2:$B$8=200),INDEX($D$2:D8+$E$2:$E$8,0))
    Last edited by martindwilson; 12-05-2010 at 12:39 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  6. #6
    Registered User
    Join Date
    11-18-2010
    Location
    Johannesburg South Africa
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Using Vlookup to find several items and then sum these items

    Have you considered using filters along with subtotal funktions? If your application can accommedate this method and you need to know how how I will gladly send you an example.

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1