Conditional calculation?

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