Overthinking the formula?

samilynn

Board Regular
Joined
Jun 24, 2003
Messages
192
Office Version
  1. 2016
Platform
  1. Windows
Am I overthinking this? It "feels" like it should be very simple, but I'm having a hard time wrapping my brain around it:

In one tab of a workbook, I have a list of products and their respective price. In a different tab, I enter a customer, the product number, and a qty they are purchasing. I need to create a formula that tells me the order value of just the products they ordered. I know I can do it with a combination of vlookups in other columns, but can I just use one formula to get the total, without creating other columns?
I'm showing samples of the two tables below. Thank you so much for your help!!
Samantha

This is the sheet with the "supporting" product and price list:

ITEMBOX QTYPRICE
A112310$8.00
A11245$12.50
A112550$3.99
A112625$1.45


This is the sheet where I create the order:

ITEM ORDEREDQTY ORDEREDTOTAL ORDER VALUE
A1123100$1,525.00
A1126500
 
Am I overthinking this? It "feels" like it should be very simple, but I'm having a hard time wrapping my brain around it:

In one tab of a workbook, I have a list of products and their respective price. In a different tab, I enter a customer, the product number, and a qty they are purchasing. I need to create a formula that tells me the order value of just the products they ordered. I know I can do it with a combination of vlookups in other columns, but can I just use one formula to get the total, without creating other columns?
I'm showing samples of the two tables below. Thank you so much for your help!!
Samantha

This is the sheet with the "supporting" product and price list:

ITEMBOX QTYPRICE
A112310$8.00
A11245$12.50
A112550$3.99
A112625$1.45


This is the sheet where I create the order:

ITEM ORDEREDQTY ORDEREDTOTAL ORDER VALUE
A1123100$1,525.00
A1126500
You may have a very good reason for not showing the line price on the order / invoice but I would like to see this as a customer. It is customary to show this detail.

Any helper columns can be hidden.

Are you still on version 2016?
 
Upvote 0
Hi, here's an option, it probably requires committing with CTRL+SHIFT+ENTER with Excel 2016.

Book3
ABCDEFGH
1ITEMBOX QTYPRICEITEM ORDEREDQTY ORDEREDTOTAL ORDER VALUE
2A1123108A11231001525
3A1124512.5A1126500
4A1125503.99
5A1126251.45
Sheet1
Cell Formulas
RangeFormula
H2H2=SUMPRODUCT(VLOOKUP(F2:F3,A2:C5,3,0),G2:G3)
 
Upvote 0
I modified FormR's already completed answer slightly so that you don't have to rewrite the formula every time an order has a different number of rows. The potential benefit depends on how the order sheet is used.

The range F2:F10 is for the order lines and counta() counts how many of them are actually in use.
The range A2:C5 is price list


Book1
ABCDEFGH
1ITEMBOX QTYPRICEITEM ORDEREDQTY ORDEREDTOTAL ORDER VALUE
2A1123108,00A11231001986,5
3A1124512,50A1126500
4A1125503,99A1125100
5A1126251,45A11245
Sheet3 (2)
Cell Formulas
RangeFormula
H2H2=SUMPRODUCT(VLOOKUP(F2:OFFSET(F2,COUNTA(F2:F10)-1,0),A2:C5,3,0),G2:OFFSET(G2,COUNTA(F2:F10)-1,0))


My apologies for any quirks, English is not my native language. "So I blame Google translate for all my goofs." :devilish:
 
Upvote 0
You could also use an extended range like this, which I'm almost certain needs to be array entered in XL2016.

Book4
ABCDEFGH
1ITEMBOX QTYPRICEITEM ORDEREDQTY ORDEREDTOTAL ORDER VALUE
2A1123108A11231001525
3A1124512.5A1126500
4A1125503.99
5A1126251.45
Sheet1
Cell Formulas
RangeFormula
H2H2=SUMPRODUCT(IFERROR(VLOOKUP(F2:F10,A2:C10,3,0),0),G2:G10)
 
Upvote 0
Hi, so in Excel 2016 (which I have to use), I get #VALUE! whether I use CTRL+SHIFT+ENTER or just ENTER.
In Excel 365 it works with ENTER.
Any suggestions on how to get it to work in 2016, please?

Thank you!
Samantha
 
Upvote 0
What is the correct calculation?
Is the order 100 boxes of 10 or 100 units thus 10 boxes?

Book1
ABCDEFGH
1ITEMBOX QTYPRICEITEM ORDEREDQTY ORDEREDTOTAL ORDER VALUE
2A1123108A112310080.00
3A1124512.5A112650029.00
4A1125503.99 
5A1126251.45 
Sheet1
Cell Formulas
RangeFormula
H2:H5H2=IF(OR(F2="",G2=""),"",VLOOKUP(F2,$A$2:$C$5,3,0)*G2/VLOOKUP(F2,$A$2:$C$5,2,0))
 
Upvote 0
in Excel 2016 (which I have to use), I get #VALUE! whether I use CTRL+SHIFT+ENTER or just ENTER.

Hi, I don't think there's anything in the suggestion that wouldn't work in XL2016 (but I can't test it) - I think it's more likely to be something with your data. Perhaps you can post a sample of your spreadsheet demonstrating the problem with the XL2BB add-in?
 
Upvote 0
What is the correct calculation?
Is the order 100 boxes of 10 or 100 units thus 10 boxes?

Book1
ABCDEFGH
1ITEMBOX QTYPRICEITEM ORDEREDQTY ORDEREDTOTAL ORDER VALUE
2A1123108A112310080.00
3A1124512.5A112650029.00
4A1125503.99 
5A1126251.45 
Sheet1
Cell Formulas
RangeFormula
H2:H5H2=IF(OR(F2="",G2=""),"",VLOOKUP(F2,$A$2:$C$5,3,0)*G2/VLOOKUP(F2,$A$2:$C$5,2,0))

Hi Dave, it's 100 units (so 10 boxes)
 
Upvote 0
Hi, I don't think there's anything in the suggestion that wouldn't work in XL2016 (but I can't test it) - I think it's more likely to be something with your data. Perhaps you can post a sample of your spreadsheet demonstrating the problem with the XL2BB add-in?
Hi, so I literally used the same exact spreadsheet, no changes to it. Opened it in 365, and it works with ENTER. Opened it in 2016, and I get the #VALUE! error.

Here is the sample from the spreadsheet, thanks again!!
Sammy

test.xlsm
ABCDEFGHIJ
1A112310$8.00A112350#VALUE!
2A11245$12.50A112650
3A112550$3.99#VALUE!
4A112625$1.45
5#VALUE!
6
Sheet1
Cell Formulas
RangeFormula
I1I1=SUMPRODUCT(VLOOKUP(F1:OFFSET(F1,COUNTA(F1:F10)-1,0),A1:C10,3,0),G1:OFFSET(G1,COUNTA(F1:F10)-1,0))
I3I3=SUMPRODUCT(IFERROR(VLOOKUP(F1:F10,A1:C10,3,0),0),G1:G10)
I5I5=SUMPRODUCT(VLOOKUP(F1:OFFSET(F1,COUNTA(F1:F10)-1,0),A1:C10,3,0),G1:OFFSET(G1,COUNTA(F1:F10)-1,0))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top