Commission Calculation using Sumifs

**tsummers2** · 12-27-2010, 08:35 PM

Need help calculating commissions. I'm thinking I can use the SUMIFS function. For example, there is a commission range of $$0-$200,000 is 35%; $201,000-$300,000 is 40%; $300,001-$400,000 is 45% and so on. I need to be able to calculate when the % changes as the gross profit is earned. I've attached my spreadsheet to assist.

Thanks!

**MarvinP** · 12-27-2010, 08:53 PM

Hi tsummers2,

It might be a lot easier to use a VLookup table. When the last argument is set to true is gives the last row that worked. See the attached with yellow fill for the example.

**DonkeyOte** · 12-28-2010, 09:11 AM

tsummers2, note we have several question forums - please choose the appropriate forum.

thread moved from Outlook to Excel: Worksheet Functions

**arthurbr** · 12-28-2010, 09:49 AM

Another interesting technique uses "differential rates", the final formula being

Please Login or Register  to view this content.

see the reddish zone for details in the attached file - You can name the ranges if needed or/and make them dynamic etc..

Not being proficient in 2007 I didn't use SUMIFS, but I suppose it can be done without difficulty

PS if needed replace the ; by , in the formula to account for regional settings