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

  • Pauline J
    Pauline J ✭✭✭✭
    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

  • Pauline J
    Pauline J ✭✭✭✭
    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"))


  • Miriam
    Miriam ✭✭

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

  • Pauline J
    Pauline J ✭✭✭✭

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

    Pauline

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!