Conditional calculation?

Melissa Wittig

I am trying to create a formula that will calculate sales commission based on which marketer is named in another column.

I have two marketers who are at different commission levels. I would like the Commission column to automatically calculate the commission total from the dollar value in the Services column, based on which marketer is listed in the Marketer column.

Additionally, the commission percentage changes based on whether the Services type was Mitigation or Reconstruction.

The conditions are as follows:

If Marketer = Jerry, and Service Type = Mitigation, then multiply Services by 3% and populate the cell with that total.

If Marketer = Jerry, and Service Type = Reconstruction, then multiply Services by 1.5%

If Marketer = Melissa, and Service Type = Mitigation, then multiply Services by 1%.

If Marketer = Melissa, and Service Type = Reconstruction, then multiply Services by 0.5%.

If Marketer = Team Split, and Service Type = Mitigation, then multiply Services by 4%.

 

Can anyone help me figure out what formulas I need and how to nest them? I hope I asked this in a way that makes sense.Thank you so much!!

Chris McKay

Hi Melissa,

Looks like you're getting a raw deal in terms of commission :-)

You've structured your question really well and so your challenge was easy to understand.

To make future admin an easier prospect, instead of hard-coding values in your formulas I'd suggest creating a Services sheet with your commission and service type values. This will allow you to change commission values or add new marketers on the fly without having to edit a long list of formulas. Trust me, it's worth the little extra effort in the long run.

So if we look at the new sheet, you'd want a Marketer column (you can use the default Primary column), a Service Type column and finally a Value column (ideally formatted as a %). We could have created a column for each service type or each marketer, but again this is easier for future administration management.

You'll then create a new row with a Marketer, Service Type and Value until you have created a matrix like the below:

Jerry | Mitigation | 3%

Jerry | Reconstruction | 1.5%

Melissa | Mitigation | 1%

Melissa | Reconstruction | 0.5%

Team | Mitigation | 4%

Finally, let's create a Combined column to the right (that can be locked and hidden) with this formula:

=Marketer@row + " " + [Service Type]@row

Now, back in your original sheet we'll need to do some work on the sheet structure. Smartsheet does not support multiple lookup values (this would need an array formula), so we'll have to replicate what we had in the Combined column (that can also be locked and hidden) in your Services sheet and add this formula:

=Marketer@row + " " + [Service Type]@row

Finally, use the following formula in the Commission column (assuming the existence of Marketer, Services and Service Type columns in this sheet):

=SUM(Services@row * INDEX({Services Value}, MATCH(Combined@row, {Services Combined}, 0)))

Anything with a curly bracket is a cross-sheet reference and can be specified when you're typing in the formula. It will ask you what sheet you are referencing and you simply need to point it to the Services sheet we created and highlight the relevant column header (so the whole column is selected).

Voila! You have your commission value and nothing has been hard coded.

Hope this helps.

Kind regards,

Chris McKay

Paul Newcome

While Chris has made a good suggestion (flexibility is almost always a good thing), if you are not worried about flexibility and want to just plug in a formula, you can use the following (see picture for positive testing):

 

=IF(AND(Marketer@row = "Jerry", [Service Type]@row = "Mitigation"), Services@row * 0.03, IF(AND(Marketer@row = "Jerry", [Service Type]@row = "Reconstruction"), Services@row * 0.015, IF(AND(Marketer@row = "Melissa", [Service Type]@row = "Mitigation"), Services@row * 0.01, IF(AND(Marketer@row = "Melissa", [Service Type]@row = "Reconstruction"), Services@row * 0.005, IF(AND(Marketer@row = "Team Split", [Service Type]@row = "Mitigation"), Services@row * 0.04)))))

 

If you wanted to add in a commission for "Team Split" and "Reconstruction", you can just add this between the last 4 and the parenthesis. Then you would just have to adjust your percentage.:

 

, IF(AND(Marketer@row = "Team Split", [Service Type]@row = "Reconstruction"), Services@row * 0.04)