Formula to get sheet summary to update percentage complete based on category value

Miriam

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!

Pauline J

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"))

Pauline J

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"))

Miriam

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

Pauline J

I'm so glad it worked for you! Happy computing!

Pauline