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 drop-down 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 drop-down 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
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!