Project Manager Pay based on 2 different rates

I am trying to figure out the best way to calculate monthly Project billing based on 2 different pay rates from 2 Project Managers.

The Project Managers are asked to enter their time for each Project monthly as a % of their total time spent. I have another column that auto calculates that Month's Bill Amount based on that %:

=[January 2022 PM Time]@row * 18286.67

I now need to account for a new Rate of $14404 for the 2nd Project Manager

Based of my Assigned to Column (Lisa =18286.67 and Brand =14404), I need to write formula that will pick the correct Pay Rate.

I'm thinking this can be done with an IF statement, but I can't quite get it to work for me hence my post here.

Thank you in advance for any guidance!!!

Best Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @SChristJLL

    You're right on the IF statement. If the contact name in the contact column contains both a first and last name you will need to edit the formula and insert the last name. Edit my Assigned column name if it is not the same as yours.

    This formula first looks to make sure something is in the Assigned column

    =IF(Assigned@row<>"", IF(Assigned@row="Lisa", [January 2022 PM Time]@row*18286.67, [January 2022 PM Time]@row*14404))

    Does this work for you?

    Kelly

  • SChristJLL
    SChristJLL ✭✭✭
    Answer ✓

    Thank you for quick reply Kelly! You definitely got me pointed in the right direction!

    Your formula was giving me a Syntax error, but I was able to tweak and got it to work :)

    Here is what I came up with:

    =IF([Assigned To]@row = "Lisa Peterson", [January 2022 PM Time]@row * 18286.67, [January 2022 PM Time]@row * 14404)


Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @SChristJLL

    You're right on the IF statement. If the contact name in the contact column contains both a first and last name you will need to edit the formula and insert the last name. Edit my Assigned column name if it is not the same as yours.

    This formula first looks to make sure something is in the Assigned column

    =IF(Assigned@row<>"", IF(Assigned@row="Lisa", [January 2022 PM Time]@row*18286.67, [January 2022 PM Time]@row*14404))

    Does this work for you?

    Kelly

  • SChristJLL
    SChristJLL ✭✭✭
    Answer ✓

    Thank you for quick reply Kelly! You definitely got me pointed in the right direction!

    Your formula was giving me a Syntax error, but I was able to tweak and got it to work :)

    Here is what I came up with:

    =IF([Assigned To]@row = "Lisa Peterson", [January 2022 PM Time]@row * 18286.67, [January 2022 PM Time]@row * 14404)


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!