Need help with formula for calculating fees based on product selected from list

Good afternoon,

I'm trying to figure out how to write a formula that if a product in column B is entered from a list of products on sheet 2, then it will calculate the fees based on the cost of the and qty is cells E and D.

So basically if I select Win 7 for the product, then it will multiple the price x qty x 10% to give me the fees i'd receive for selling the balloon.

In addition to that formula I need one to do the same thing for a Win 7 bundled product, so if I selected a bundle it would multiple the cost of the bundle X 17% to give me the individual cost of win 7 of the bundle then multiple that X qty X 10% to give me the fees.

I'm attaching my spreadsheet, I've tried average, sumif and countif, but I don't understand how to calculate the fees after it finds the product in my list.

Can anyone please help.

Originally Posted by FDibbins

If i understand you correctly (not sure I do though), try the following formula in A4...

=IF(B4="","",(D4*E4)*1.1)

If all you want is your 10% profit, change the 1.1 to 0.1

How do you determine if you have sold a "bundle"?

It's based off what the rep quotes, so we would know if they sold the bundle or not. But will "","" allow me to pick from the list on sheet #2?

Basically depending on which product they pick off the list it will figure the fees they received by that product. Some are bundled, and some are individual and have different pay %'s.

I apologize if i'm not explaining correctly.

Here is the way it works. I have a report, it shows me what products have been sold and the price and qty that have been sold, based off specific products in this list that are sold there are different fees the rep receives if they sell that product. I am trying to figure out a formula based off the product they sell what the fees would be.

So if they sell Win 7 then the formula would be =average(B5="Win 7",(E5*D5*.01)) that will tell be based on that 1 product what the fees would be, but I want the formula to be able to do this from a list of products, not 1 product that I am specifically telling it to look for.

Hope this helps

Sorry the formula would be =AVERAGE(IF(B5="Win Pro",E5*D5*0.01)) for a single product, but I am looking to have it look at a list of critera.

Thanks all,

I figured it out. IF(COUNTIF formula worked perfectly.