Adding Percentages Together for Total
Hi!
I'm trying to figure out a way to add the percentages from the Column labeled "Capacity" with a function and have them automatically tally together in the "Total Percentage" column. The percent value is being calculated with the Nested IF formula that I have shown.
The point is that I will eventually add these columns to a larger sheet with all active projects. We are trying to find a way through smartsheets to automatically calculate how much capacity each individual Project Manager is operating under and visualize all the data in a dashboard. It needs to be automated as much as possible otherwise it needs to be manually updated and it defeats the whole purpose.
In a perfect smartsheet world we want to have our sheet with all active projects and the columns PME, PCR, Capacity and total percentage. Each project manager will have their projects sorted by their email address and the total percentage will be calculated somehow. Then using sheet summary take all the individual project manager data with their total percentage of capacity and visualize it with a dashboard. Sort of a more involved/complicated way of doing resource management.
I hope I explained this in a way that makes sense!
Thanks for any help provided.
Best Answer

Hi Chloe,
My apologies, I should have been more clear! Adding the quotation marks around the decimals treats it as text again, so it won't format properly. So instead of ".5", try 0.5, no quotes.
Try this, with just the numbers:
=IF(AND(PME@row = "easy", PCR@row = "intermediate"), 0.5, (IF(AND(PME@row = "easy", PCR@row = "easy"), 0.25, IF(AND(PME@row = "easy", PCR@row = "advanced"), 1, IF(AND(PME@row = "intermediate", PCR@row = "easy"), .1, (IF(AND(PME@row = "intermediate", PCR@row = "intermediate"), 0.2, IF(AND(PME@row = "intermediate", PCR@row = "advanced"), 0.5, IF(AND(PME@row = "advanced", PCR@row = "easy"), 0.1, (IF(AND(PME@row = "advanced", PCR@row = "intermediate"), 0.15, IF(AND(PME@row = "advanced", PCR@row = "advanced"), 0.25, IF(AND(PME@row = "experts only", PCR@row = "easy"), 0.1, (IF(AND(PME@row = "experts only", PCR@row = "intermediate"), 0.15, IF(AND(PME@row = "experts only", PCR@row = "advanced"), 0.25, IF(AND(PME@row = "experts only", PCR@row = "experts only"), 0.25, 0)))))))))))))))))
You can tell if it's working because the numbers should appear on the right of the cell. Text appears on the left, whereas numerical values appear to the right.
Let me know if this works!
Genevieve
Answers

I think where you may be having issues is that the percent is being returned as text, since in your Nested If you have "50%" written in the formula. Percents in formulas need to be written as decimals in order to be identified as numerical (see here), then the Capacity column would be formatted to add in the % sign... ex:
=IF(AND(PME@row = "easy", PCR@row = "intermediate"), 0.5, IF..... etc
You could either rewrite this formula with decimals, or you could use a formula in that Total Percentage column that looks at the value in the Capacity column in its row and return the numeric version:
=IF(Capacity@row = "100%", 1, VALUE(LEFT((Capacity@row), 2)) / 100)
Since the LEFT function is only grabbing the 2 numbers on the left of the cell, if the value is actually 100 it would read it as 10... this is why I added the additional IF statement rule at the beginning. This formula will return a decimal in the Total Percentage column. You can then format the column as a % and it will adjust this.
Then at the top of this column you could use a SUM function and it will work on the numbers, now! You could also do SUMIF statement if you only wanted to sum the percent based on one specific Project Manager, referencing this helper column so it has numerical data to sum. Let me know if this makes sense, or if I've misunderstood your question!
Cheers,
Genevieve

Hi Genevive,
Thanks for your reply. I have no idea why this is so hard for me! But here's what I did trying to follow the directions you gave:
Changed the NestedIF to decimals:
=IF(AND(PME@row = "easy", PCR@row = "intermediate"), ".5", (IF(AND(PME@row = "easy", PCR@row = "easy"), ".25", IF(AND(PME@row = "easy", PCR@row = "advanced"), "1", IF(AND(PME@row = "intermediate", PCR@row = "easy"), ".1", (IF(AND(PME@row = "intermediate", PCR@row = "intermediate"), ".2", IF(AND(PME@row = "intermediate", PCR@row = "advanced"), ".5", IF(AND(PME@row = "advanced", PCR@row = "easy"), ".1", (IF(AND(PME@row = "advanced", PCR@row = "intermediate"), ".15", IF(AND(PME@row = "advanced", PCR@row = "advanced"), ".25", IF(AND(PME@row = "experts only", PCR@row = "easy"), ".1", (IF(AND(PME@row = "experts only", PCR@row = "intermediate"), ".15", IF(AND(PME@row = "experts only", PCR@row = "advanced"), ".25", IF(AND(PME@row = "experts only", PCR@row = "experts only"), ".25", "0")))))))))))))))))
I've tried to make the Capacity column format as percent by highlighting the entire column and hitting the % symbol on the task bar. It doesn't change anything. The numbers still show up as decimals.
Then I tried to do the SUMIF again, it still shows up as 0.

Hi Chloe,
My apologies, I should have been more clear! Adding the quotation marks around the decimals treats it as text again, so it won't format properly. So instead of ".5", try 0.5, no quotes.
Try this, with just the numbers:
=IF(AND(PME@row = "easy", PCR@row = "intermediate"), 0.5, (IF(AND(PME@row = "easy", PCR@row = "easy"), 0.25, IF(AND(PME@row = "easy", PCR@row = "advanced"), 1, IF(AND(PME@row = "intermediate", PCR@row = "easy"), .1, (IF(AND(PME@row = "intermediate", PCR@row = "intermediate"), 0.2, IF(AND(PME@row = "intermediate", PCR@row = "advanced"), 0.5, IF(AND(PME@row = "advanced", PCR@row = "easy"), 0.1, (IF(AND(PME@row = "advanced", PCR@row = "intermediate"), 0.15, IF(AND(PME@row = "advanced", PCR@row = "advanced"), 0.25, IF(AND(PME@row = "experts only", PCR@row = "easy"), 0.1, (IF(AND(PME@row = "experts only", PCR@row = "intermediate"), 0.15, IF(AND(PME@row = "experts only", PCR@row = "advanced"), 0.25, IF(AND(PME@row = "experts only", PCR@row = "experts only"), 0.25, 0)))))))))))))))))
You can tell if it's working because the numbers should appear on the right of the cell. Text appears on the left, whereas numerical values appear to the right.
Let me know if this works!
Genevieve

Oh my goodness! It worked!
Thank you so much for your help!

So glad to hear that! No problem at all 🙂
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.8K Get Help
 65 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!