Multiplication based on multiple variables

**auspidermonkey** · 03-23-2017, 08:36 AM

Hi,

I have looked high and low for a solution, I have searched for all variables I can think of and sadly have not been able to find anything online and at a complete standstill.

My problem. I am trying to build an inventory spreadsheet which will automatically calculate how much an item is sold for.

My admin tab will contain prices set out like below (this is up for changes too if there is a better way):

Product	Retail	Wholesale	Discount	Additional
Chocolate	2	1	1.5
Soda	1.5	1	1.2
Cake	10	5	7
Juice	2.5	1.5	2

On my Sales tab I will have two pull down lists one for product, one for Buyer and one third for how many items sold. I am trying to create a formula which will calculate number of items sold multiplied by sale price (so if they purchased 5 and chose Cake & Discount it would calculate 5 * 7, Cake & Retail would be 5*10, etc).

I have managed to do this using nested if statements, while this works it is very limited and not expandable (adding just 1 more product or buyer would exponentially add more nested ifs).

Ideally I would like (maybe) the formula role down a list of products and buyers until empty and calculate on that.

I'm close to giving up and any and all help is much appreciated. If I have missed out any information let me know. On that if what I've typed is totally confusing let me know too and I'll try to redo it. Also, it this has been asked previously apologies for not being able to find it... If you can send me the link to the thread it'd be appreciated.

Thanks in advance
Dave

**davesexcel** · 03-23-2017, 08:52 AM

Attaching a sample workbook enables others to work on your problem:

To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and click "manage attachments" to open the upload window.

To add a file to a post

**JohnTopley** · 03-23-2017, 09:00 AM

Try

=INDEX($A2:$D5,MATCH(Product,$A2:$A5,0),MATCH(Buyer,$A$1:$D$1,0))*Quantity

Assuming table in your post is in range A1:D5

Product= e.g. "Chocolate"

Buyer= e.g "Retail"

OR

=VLOOKUP(Product,$A$2:$D$5,MATCH(Buyer,$A$1:$D$1,0),0)*Quantity

Amended .....

**Aardigspook** · 03-23-2017, 09:45 AM

If you're using drop-down lists, then you can replace the 'Product' and 'Buyer' in John's formulae above with references to those cells. Also, assuming that your formula is going on your Sales sheet, the references to this table on your Admin sheet will need to include the sheet name. So you'll end up with something like this:
=INDEX(Admin!$A2:$D5,MATCH(A2,Admin!$A2:$A5,0),MATCH(B2,Admin!$A$1:$D$1,0))*C2
where A2 is your 'Product' drop-down cell, B2 is your 'Buyer' drop-down and C2 is where the quantity is listed.

Hope that makes sense...

**auspidermonkey** · 03-29-2017, 03:57 AM

Thanks for the suggestions guys (also sorry for slow reply - I work on a remote base in Africa and have limited internet usage). I will try these suggestions and if I can't get them to work I will attach a demo file as per davesexcel suggests.

Thanks again.

Fingers crossed I can figure it all out!!

**auspidermonkey** · 03-30-2017, 07:01 AM

Hey guys,

I have tried both suggestions and messed around with the formulas a bit and all I can seem to get it to respond with is either ?NAME or a number (not useful number; just a 1 or 2 which doesn't correspond to any of the prices).

I will attach a SUPER basic file as I have just managed to delete my master file (and of course true to fan and sh*t colliding I have no backup) that I have been working on for the past few hours (insert countless face palms and head plants here).

Thanks again guys. I'm starting to regret taking this what I thought to be simple project on.