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
 10.7K Get Help
 63 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!