SUM and VLOOKUP (I think!)

Orbus

New Member
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 SOLDSKUVALUE I WOULD LIKE RETURNEDVLOOKUP COLUMN: SKUVLOOKUP COLUMN: QTY SOLD = 1VLOOKUP COLUMN: QTY SOLD = EACH ADDITIONAL ITEM
1SKU-TEST110SKU-TEST1109
2SKU-TEST119
3SKU-TEST128

<tbody>
</tbody>


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?
 

CyrusTheVirus

Well-known Member
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)))
 

Orbus

New Member
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!
 

dazkangel

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

Orbus

New Member
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:

Matty

Well-known Member
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
 

Orbus

New Member
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?
 

Orbus

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

Rich (BB 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.
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...
Top