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

 

[Dave Patton]

Did you try the example that I posted?
N.B. You can copy the example and paste it to a clean sheet. Click on the icon below the f(x) in the heading.

 

[FormR]

Hi, I fear that it's not the most efficient of choices, but you could try like this - important, this assumes the items are only in column A once.

Book1
	A	B	C	D	E	F	G	H	I
1	A1123	10	8			A1123	50		472.5
2	A1124	5	12.5			A1126	50
3	A1125	50	3.99
4	A1126	25	1.45
Sheet1

Cell Formulas
Range	Formula
I1	I1	=SUMPRODUCT(SUMIF(A1:A4,F1:F4,C1:C4),G1:G4)

 

[samilynn]

Hi, I fear that it's not the most efficient of choices, but you could try like this - important, this assumes the items are only in column A once.

Book1
	A	B	C	D	E	F	G	H	I
1	A1123	10	8			A1123	50		472.5
2	A1124	5	12.5			A1126	50
3	A1125	50	3.99
4	A1126	25	1.45
Sheet1

Cell Formulas
Range	Formula
I1	I1	=SUMPRODUCT(SUMIF(A1:A4,F1:F4,C1:C4),G1:G4)

Hi, I think this works!! But I have one other question, as it's part of my file too, and I hope it's not more complicated:

Using the same table, I have to compute, as an example: if a customer buys a box of the first item (10 pieces), then they get $8.00 back. And on and on, based on the table. How do I modify the formula to say that? See the table below:

test.xlsm
	A	B	C	D	E	F	G	H
1	ITEM	BOX QTY	BONUS		ORDERED ITEM	ORDER QTY
2	A1123	10	$8.00		A1123	50
3	A1124	5	$12.50		A1126	50
4	A1125	50	$3.99
5	A1126	25	$1.45
6					TOTAL BONUS RECEIVED
7					$40.00
8					$2.90
9					$42.90
10
11
Sheet1

Cell Formulas
Range	Formula
E7	E7	=F2/B2*C2
E8	E8	=F3/B5*C5
E9	E9	=SUM(E7:E8)

 

[FormR]

Hi, something like this maybe.. again, I don't think this is a very efficient method - you should try to keep the number of rows referenced as small as possible.

Book1
	A	B	C	D	E	F
1	ITEM	BOX QTY	BONUS		ORDERED ITEM	ORDER QTY
2	A1123	10	8		A1123	50
3	A1124	5	12.5		A1126	50
4	A1125	50	3.99
5	A1126	25	1.45
6					TOTAL BONUS RECEIVED
7					40
8					2.9
9					42.90
Sheet1

Cell Formulas
Range	Formula
E7	E7	=F2/B2*C2
E8	E8	=F3/B5*C5
E9	E9	=SUMPRODUCT(IFERROR(F1:F5/SUMIF(A1:A5,E1:E5,B1:B5),0),SUMIF(A1:A5,E1:E5,C1:C5))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[samilynn]

Hi, something like this maybe.. again, I don't think this is a very efficient method - you should try to keep the number of rows referenced as small as possible.

Book1
	A	B	C	D	E	F
1	ITEM	BOX QTY	BONUS		ORDERED ITEM	ORDER QTY
2	A1123	10	8		A1123	50
3	A1124	5	12.5		A1126	50
4	A1125	50	3.99
5	A1126	25	1.45
6					TOTAL BONUS RECEIVED
7					40
8					2.9
9					42.90
Sheet1

Cell Formulas
Range	Formula
E7	E7	=F2/B2*C2
E8	E8	=F3/B5*C5
E9	E9	=SUMPRODUCT(IFERROR(F1:F5/SUMIF(A1:A5,E1:E5,B1:B5),0),SUMIF(A1:A5,E1:E5,C1:C5))
Press CTRL+SHIFT+ENTER to enter array formulas.

Thank you so much, this looks like it does the trick!!
Much appreciated!
Sammy