Simple SUMIF
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
-
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
-
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)
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.2K Get Help
- 359 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 135 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!