Simple SUMIF

Philip Arnfield

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

Philip Arnfield

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)

Paul Newcome

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

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)