Formula to get sheet summary to update percentage complete based on category value
Hello!
I am trying to manipulate a formula in my sheet summary to return a percentage complete based on the value selected in the dropdown menu I created. The formula I've tried (and a few variations of it) is:
=100 * COUNTIFS([Review Complete]:[Review Complete], 1) / COUNTM([Primary Practice Area]:[Primary Practice Area], HAS([Primary Practice Area]#))
Basically, in my sheet I have a column called "Primary Practice Area" and I have a checkbox column of "Review Complete". The "Primary Practice Area" column is a dropdown list and my sheet summary has a field with the same name and same dropdown options. I want to be able to select one of the values in the summary, and return a total percentage of how many of that value have a marked checkbox in the "Review Complete" column. This formula is giving me the total complete of the entire sheet regardless of which dropdown value I select in the summary. I have tried to find other questions that have been answered in the same way, but perhaps I am not wording my inquiry correctly.
Thank you in advance!
Best Answer

Hi Miriam,
I have some similar calculations in a Sheet Summary. But it sounds like you have a dropdown list in a Sheet Summary field? I'm not familiar with that...
I created a test sheet, below is a screen shot. I created 1 field to calculate the % Complete, and then two helper fields to validate that the first field is calculating correctly, especially as I check and uncheck the Completed field. Once you get your formula working you wouldn't necessarily need the Sheet Summary helper fields.
This is the formula I use for the Practice A% Complete (and I think you may need a summary field for each unique practice, if it isn't too many to create). Let me know if this helps!
Pauline
=COUNTIFS(PrimPracArea:PrimPracArea, ="Practice A", Completed:Completed, =1) / (COUNTIF(PrimPracArea:PrimPracArea, ="Practice A"))
Answers

Hi Miriam,
I have some similar calculations in a Sheet Summary. But it sounds like you have a dropdown list in a Sheet Summary field? I'm not familiar with that...
I created a test sheet, below is a screen shot. I created 1 field to calculate the % Complete, and then two helper fields to validate that the first field is calculating correctly, especially as I check and uncheck the Completed field. Once you get your formula working you wouldn't necessarily need the Sheet Summary helper fields.
This is the formula I use for the Practice A% Complete (and I think you may need a summary field for each unique practice, if it isn't too many to create). Let me know if this helps!
Pauline
=COUNTIFS(PrimPracArea:PrimPracArea, ="Practice A", Completed:Completed, =1) / (COUNTIF(PrimPracArea:PrimPracArea, ="Practice A"))

Thank you so much, Pauline! I am testing it out, and it's doing what we need. I appreciate your assistance!

I'm so glad it worked for you! Happy computing!
Pauline
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 348 Global Discussions
 199 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 455 Show & Tell
 28 Member Spotlight
 1 SmartStories
 282 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!