Forum Discussion
Trying to figure out how to calculate various commission rates on the same spreadsheet
Hi,
I am trying to figure out how to add a column to calculate commissions on an excel spreadsheet that has multiple items (with several orders for each item) and each have a different price and commission rate. For example:
Product A sells for $10 and generates a 2% commission
Product B sells for $12 and generates 1.7% commission
Product C sells for $9 and generates 2.2% commission
It seems when I create a formula, it somehow uses the same commission rate for all items. Can I create formulas to calculate the different commissions for different items within the same spreadhseet? I have 400 orders of five different products ordered over the last three months I need to calculate commissions for and there has to be an easy way to do this? I can't sort by item and calculate commissions that way because I need to sort by date as these items are ordered all the time and I need to keep them in order by month (all the orders for Sept. together, orders for Oct. together, etc.)
Thanks
7 Replies
The formula
= SUMIFS( Sales[Units], Sales[Product], PriceList[Product] )
will generate an array of units sold with a value for each product shown in the price list. Multiplying that by the corresponding prices and commission rates allows the commission to be calculated:
= SUMPRODUCT(
SUMIFS(Sales[Units], Sales[Product], PriceList[Product] ),
PriceList[Price],
PriceList[CommissionRate] )
You might consider a more dynamic solution by creating a product list with prices and commission %. Then you record all Sales transaction in another list and pick-up the prices and commissions from the other list. You can then create a, for example, a pivot table to summarise sales and commissions by month. Have a look at the attached workbook to see if this meets your requirements.
- Its pretty easy, simply multiply all cells and drag formula down till last cell of column.
Eg. Your table has 3 columns, column A Product, Column B Price, Column C Commission. In new column use this formula B1*C1
This will definitely give you commition%- Thank you.
