Overthinking the formula?

[samilynn]

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:

ITEM	BOX QTY	PRICE
A1123	10	$8.00
A1124	5	$12.50
A1125	50	$3.99
A1126	25	$1.45

This is the sheet where I create the order:

ITEM ORDERED	QTY ORDERED	TOTAL ORDER VALUE
A1123	100	$1,525.00
A1126	500

 

[HighAndWilder]

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:

ITEM	BOX QTY	PRICE
A1123	10	$8.00
A1124	5	$12.50
A1125	50	$3.99
A1126	25	$1.45

This is the sheet where I create the order:

ITEM ORDERED	QTY ORDERED	TOTAL ORDER VALUE
A1123	100	$1,525.00
A1126	500

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?

 

[FormR]

Hi, here's an option, it probably requires committing with CTRL+SHIFT+ENTER with Excel 2016.

Book3
	A	B	C	D	E	F	G	H
1	ITEM	BOX QTY	PRICE			ITEM ORDERED	QTY ORDERED	TOTAL ORDER VALUE
2	A1123	10	8			A1123	100	1525
3	A1124	5	12.5			A1126	500
4	A1125	50	3.99
5	A1126	25	1.45
Sheet1

Cell Formulas
Range	Formula
H2	H2	=SUMPRODUCT(VLOOKUP(F2:F3,A2:C5,3,0),G2:G3)

 

[Tupe77]

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
	A	B	C	D	E	F	G	H
1	ITEM	BOX QTY	PRICE			ITEM ORDERED	QTY ORDERED	TOTAL ORDER VALUE
2	A1123	10	8,00			A1123	100	1986,5
3	A1124	5	12,50			A1126	500
4	A1125	50	3,99			A1125	100
5	A1126	25	1,45			A1124	5
Sheet3 (2)

Cell Formulas
Range	Formula
H2	H2	=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."

 

[FormR]

You could also use an extended range like this, which I'm almost certain needs to be array entered in XL2016.

Book4
	A	B	C	D	E	F	G	H
1	ITEM	BOX QTY	PRICE			ITEM ORDERED	QTY ORDERED	TOTAL ORDER VALUE
2	A1123	10	8			A1123	100	1525
3	A1124	5	12.5			A1126	500
4	A1125	50	3.99
5	A1126	25	1.45
Sheet1

Cell Formulas
Range	Formula
H2	H2	=SUMPRODUCT(IFERROR(VLOOKUP(F2:F10,A2:C10,3,0),0),G2:G10)

 

[samilynn]

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

 

[Dave Patton]

What is the correct calculation?
Is the order 100 boxes of 10 or 100 units thus 10 boxes?

Book1
	A	B	C	D	E	F	G	H
1	ITEM	BOX QTY	PRICE			ITEM ORDERED	QTY ORDERED	TOTAL ORDER VALUE
2	A1123	10	8			A1123	100	80.00
3	A1124	5	12.5			A1126	500	29.00
4	A1125	50	3.99
5	A1126	25	1.45
Sheet1

Cell Formulas
Range	Formula
H2:H5	H2	=IF(OR(F2="",G2=""),"",VLOOKUP(F2,$A$2:$C$5,3,0)*G2/VLOOKUP(F2,$A$2:$C$5,2,0))

 

[FormR]

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?

 

[samilynn]

What is the correct calculation?
Is the order 100 boxes of 10 or 100 units thus 10 boxes?

Book1
	A	B	C	D	E	F	G	H
1	ITEM	BOX QTY	PRICE			ITEM ORDERED	QTY ORDERED	TOTAL ORDER VALUE
2	A1123	10	8			A1123	100	80.00
3	A1124	5	12.5			A1126	500	29.00
4	A1125	50	3.99
5	A1126	25	1.45
Sheet1

Cell Formulas
Range	Formula
H2:H5	H2	=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)

 

[samilynn]

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
	A	B	C	D	E	F	G	H	I	J
1	A1123	10	$8.00			A1123	50		#VALUE!
2	A1124	5	$12.50			A1126	50
3	A1125	50	$3.99						#VALUE!
4	A1126	25	$1.45
5									#VALUE!
6
Sheet1

Cell Formulas
Range	Formula
I1	I1	=SUMPRODUCT(VLOOKUP(F1:OFFSET(F1,COUNTA(F1:F10)-1,0),A1:C10,3,0),G1:OFFSET(G1,COUNTA(F1:F10)-1,0))
I3	I3	=SUMPRODUCT(IFERROR(VLOOKUP(F1:F10,A1:C10,3,0),0),G1:G10)
I5	I5	=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.