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
 
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.
 
Upvote 0
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
ABCDEFGHI
1A1123108A112350472.5
2A1124512.5A112650
3A1125503.99
4A1126251.45
Sheet1
Cell Formulas
RangeFormula
I1I1=SUMPRODUCT(SUMIF(A1:A4,F1:F4,C1:C4),G1:G4)
 
Upvote 0
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
ABCDEFGHI
1A1123108A112350472.5
2A1124512.5A112650
3A1125503.99
4A1126251.45
Sheet1
Cell Formulas
RangeFormula
I1I1=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
ABCDEFGH
1ITEMBOX QTYBONUSORDERED ITEMORDER QTY
2A112310$8.00A112350
3A11245$12.50A112650
4A112550$3.99
5A112625$1.45
6TOTAL BONUS RECEIVED
7$40.00
8$2.90
9$42.90
10
11
Sheet1
Cell Formulas
RangeFormula
E7E7=F2/B2*C2
E8E8=F3/B5*C5
E9E9=SUM(E7:E8)
 
Upvote 0
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
ABCDEF
1ITEMBOX QTYBONUSORDERED ITEMORDER QTY
2A1123108A112350
3A1124512.5A112650
4A1125503.99
5A1126251.45
6TOTAL BONUS RECEIVED
740
82.9
942.90
Sheet1
Cell Formulas
RangeFormula
E7E7=F2/B2*C2
E8E8=F3/B5*C5
E9E9=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.
 
Upvote 0
Solution
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
ABCDEF
1ITEMBOX QTYBONUSORDERED ITEMORDER QTY
2A1123108A112350
3A1124512.5A112650
4A1125503.99
5A1126251.45
6TOTAL BONUS RECEIVED
740
82.9
942.90
Sheet1
Cell Formulas
RangeFormula
E7E7=F2/B2*C2
E8E8=F3/B5*C5
E9E9=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
 
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