Simple SUMIF

Options

To calculate the Gross Profit per hour achieved by a Supervisor I use this formula and achieve the correct answer

=(SUMIF(Supervisor:Supervisor, "John Smith", AGP:AGP)) / (SUMIF(Supervisor:Supervisor, "John Smith", [A_Labour Hrs.]:[A_Labour Hrs.]))

AGP refers to the Gross Profit and [A_Labour_Hrs.] to actual hours.

I now want to add to the results for this supervisor 1/3 of the profit by the supervisor "Shared" - but this formula is throwing up an incorrect answer.

=(SUMIF(Supervisor:Supervisor, "John Smith", AGP:AGP))+(0.33*(SUMIF(Supervisor:Supervisor, "Shared", AGP:AGP)) / ((SUMIF(Supervisor:Supervisor, "John Smith", [A_Labour Hrs.]:[A_Labour Hrs.])+0.33*(SUMIF(Supervisor:Supervisor, "Shared", [A_Labour Hrs.]:[A_Labour Hrs.]))

As always, thank you in advance for the assistance provided

Best Answer

  • Philip Arnfield
    Philip Arnfield ✭✭
    Answer ✓
    Options

    Thank you Paul. A good nights sleep has allowed me to see my question was not clear at all. Ask the wrong question and you may well get the right answer, that is of no help to your problem. My bad. 😏

    What I wanted to do was to add 1/3 of the gross profit per hour achieved by "Shared" to the gross profit per hour achieved by "John Smith"

    A fresh mind and a fresh approach to the problem allowed me to solve it as follows...

    =((SUMIF(Supervisor:Supervisor, "John Smith", AGP:AGP)) +(SUMIF(Supervisor:Supervisor, "Shared", AGP:AGP)) * 0.33) / ((SUMIF(Supervisor:Supervisor, "John Smith", [A_Labour Hrs.]:[A_Labour Hrs.])) +(SUMIF(Supervisor:Supervisor, "Shared", [A_Labour Hrs.]:[A_Labour Hrs.])) * 0.33)

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Let's try this...

    =(SUMIF(Supervisor:Supervisor, "John Smith", AGP:AGP) * 1.33) / (SUMIF(Supervisor:Supervisor, "John Smith", [A_Labour Hrs.]:[A_Labour Hrs.]) * 1.33)

  • Philip Arnfield
    Philip Arnfield ✭✭
    Answer ✓
    Options

    Thank you Paul. A good nights sleep has allowed me to see my question was not clear at all. Ask the wrong question and you may well get the right answer, that is of no help to your problem. My bad. 😏

    What I wanted to do was to add 1/3 of the gross profit per hour achieved by "Shared" to the gross profit per hour achieved by "John Smith"

    A fresh mind and a fresh approach to the problem allowed me to solve it as follows...

    =((SUMIF(Supervisor:Supervisor, "John Smith", AGP:AGP)) +(SUMIF(Supervisor:Supervisor, "Shared", AGP:AGP)) * 0.33) / ((SUMIF(Supervisor:Supervisor, "John Smith", [A_Labour Hrs.]:[A_Labour Hrs.])) +(SUMIF(Supervisor:Supervisor, "Shared", [A_Labour Hrs.]:[A_Labour Hrs.])) * 0.33)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!