Results 1 to 10 of 10

Thread: SUM and VLOOKUP (I think!)

  1. #1
    New Member
    Join Date
    Sep 2012
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default SUM and VLOOKUP (I think!)

    Hi there, I have a sales sheet that I use VLOOKUP for to return an item value for SKU which met my needs until now. The problem that I have is that the current VLOOKUP formula that I have at present will only work out the value of one item as it only references the SKU and performs the VLOOKUP to return a value for that SKU. I also need my formula to calculate the price for multiple items which I will show below.

    QTY SOLD SKU VALUE I WOULD LIKE RETURNED VLOOKUP COLUMN: SKU VLOOKUP COLUMN: QTY SOLD = 1 VLOOKUP COLUMN: QTY SOLD = EACH ADDITIONAL ITEM
    1 SKU-TEST1 10 SKU-TEST1 10 9
    2 SKU-TEST1 19
    3 SKU-TEST1 28


    The formula would basically lookup a table and if the QTY SOLD was 1 it would return the value of 1 item (10) but for each additional item sold it would add on the corresponding value which for 2 items would be (10+9), 3 items (10+9+9) 4 items (10+9+9+9) etc.

    Is this possible?

  2. #2
    Board Regular CyrusTheVirus's Avatar
    Join Date
    Jan 2015
    Location
    Glens Falls, USA
    Posts
    722
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUM and VLOOKUP (I think!)

    Would this work for you?

    C2:
    Code:
    =IF(A2=1,VLOOKUP(B2,$E$2:$F$2,2,0),VLOOKUP(B2,$E$2:$F$2,2)+((A2-1)*VLOOKUP(B2,$E$2:$G$2,3,0)))

  3. #3
    New Member
    Join Date
    Sep 2012
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUM and VLOOKUP (I think!)

    Quote Originally Posted by CyrusTheVirus View Post
    Would this work for you?

    C2:
    Code:
    =IF(A2=1,VLOOKUP(B2,$E$2:$F$2,2,0),VLOOKUP(B2,$E$2:$F$2,2)+((A2-1)*VLOOKUP(B2,$E$2:$G$2,3,0)))
    Holy smokes dude, that is seriously awesome! I have been scratching my head about this for absolutely ages! Am I right in the following:

    =IF(A2=1,VLOOKUP(B2,$E$2:$F$2,2,0) is how it works out the price of 1 item.
    , means that if the above cell A2 is greater than 1 then perform the next calculation below
    VLOOKUP(B2,$E$2:$F$2,2)+((A2-1)*VLOOKUP(B2,$E$2:$G$2,3,0))) 1st vlookup returns value 10 then 2nd vlookup deducts 1 from A2 then multiplies it by the additional item price
    I cannot thank you enough for this, awesome contribution!

  4. #4
    Board Regular CyrusTheVirus's Avatar
    Join Date
    Jan 2015
    Location
    Glens Falls, USA
    Posts
    722
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUM and VLOOKUP (I think!)

    Hey Orbus,

    Yes, that's exactly how it works. You're welcome, glad it helped, see ya around.

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

    Default Re: SUM and VLOOKUP (I think!)

    Try:
    =SUMPRODUCT(VLOOKUP(B3;E$3:G3;{2\3};0)*IF({1\0};1;A3-1))

  6. #6
    New Member
    Join Date
    Sep 2012
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUM and VLOOKUP (I think!)

    Hi guys, I tried editing the formula that Cyrus posted however it is returning incorrect values for sales with more than 1 item. The Amount Sold is in column Column D and the SKU is in Column F. The lookup table is in a separate workbook called ProductCosts and the single item price is in Column E and additional item cost is in Column F. Currently the table is 35 rows in length but I changed the lookup to search to row 100 as we are constantly adding in new SKUs and product costs. The row that I am trying to implement the formula on is row 2644

    =IF(D2644=1,VLOOKUP(F2644,ProductCosts!$A$1:$G$100,5,0),VLOOKUP(F2644,ProductCosts!$A$1:$G$100,5)+((D2644-1)*VLOOKUP(F2644,ProductCosts!$A$1:$G$100,6,0)))

    Dazkangel, I tried editing your formula but I got absolutely nowhere as the sections where "{2\3}" and "IF{{1\0}" have completely stumped me.

    Any help is very much appreciated. Thank you very much
    Last edited by Orbus; Jun 23rd, 2019 at 05:55 AM. Reason: typo

  7. #7
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,702
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUM and VLOOKUP (I think!)

    Dazkangel, I tried editing your formula but I got absolutely nowhere as the sections where "{2\3}" and "IF{{1\0}" have completely stumped me.
    Excel Regional Settings mean that some symbols don't travel too well.

    Here's dazkangel's formula formatted in a way that hopefully works for you:

    Code:
    =SUMPRODUCT(VLOOKUP(B2,E$2:G2,{2;3},0)*IF({1;0},1,A2-1))
    (Nice take on the problem by the way, dazkangel!)

    Matty

  8. #8
    New Member
    Join Date
    Sep 2012
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUM and VLOOKUP (I think!)

    Quote Originally Posted by Matty View Post
    Excel Regional Settings mean that some symbols don't travel too well.

    Here's dazkangel's formula formatted in a way that hopefully works for you:

    Code:
    =SUMPRODUCT(VLOOKUP(B2,E$2:G2,{2;3},0)*IF({1;0},1,A2-1))
    (Nice take on the problem by the way, dazkangel!)

    Matty
    Hi Matty, apologies in advance for my Excel noobness but is there any way that you could explain what each part does so I can edit the formula to show the correct values on the sheet that I mentioned above that I use in real life as opposed to the example I gave in post 1 please?

  9. #9
    New Member
    Join Date
    Sep 2012
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUM and VLOOKUP (I think!)

    I think I have it working with the following formula as the results look correct:

    Code:
    =IF(D2642=1,VLOOKUP(F2642,ProductCosts!$A$1:$G$100,5,false),VLOOKUP(F2642,ProductCosts!$A$1:$G$100,5,false)+((D2642-1)*VLOOKUP(F2642,ProductCosts!$A$1:$G$100,6,false)))
    
    Does this look OK to everyone else?

    Matty - I tried editing the formula you provided but it kept on returning the N/A error.

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

    Default Re: SUM and VLOOKUP (I think!)

    Try again, last time I was wrong when copying. See in my drive:
    https://1drv.ms/x/s!Akz1FKD4hMCRk1uiAMaJMPwZB6W5
    =SUMPRODUCT(VLOOKUP(B2,E$2:G2,{2,3},0)*IF({1,0},1,A2-1))

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
  •