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 ...
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] )