Looking for Yes and No answer percentages

I have a sheet with 4 columns that are yes and no answers. I am looking to get percentages for each answer in each column. I would also like to put this is a summary field so I can extract it to a dashboard. I'm struggling to find the right formula for this, and was hoping someone could help.

Best Answer

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭
    Answer ✓

    Hi @jmdierking,

    I would try this formula in your Sheet Summary to capture the % for Yes:

    =COUNTIF([Column1]:[Column1], "Yes") / COUNTIF([Column1]:[Column1], OR(@cell = "Yes", @cell = "No"))

    I would use this formula to capture the percentage for No:

    =COUNTIF([Column1]:[Column1], "No") / COUNTIF([Column1]:[Column1], OR(@cell = "Yes", @cell = "No"))

    After you apply the formulas to your Sheet Summary fields, you will need to change the fields to be formatted as Percentage. To do that, click the 3 dots next to the Summary field name, select "Number Formatting", and then select "Percentage".

    Hope this helps!

    -Ray

Answers

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭
    Answer ✓

    Hi @jmdierking,

    I would try this formula in your Sheet Summary to capture the % for Yes:

    =COUNTIF([Column1]:[Column1], "Yes") / COUNTIF([Column1]:[Column1], OR(@cell = "Yes", @cell = "No"))

    I would use this formula to capture the percentage for No:

    =COUNTIF([Column1]:[Column1], "No") / COUNTIF([Column1]:[Column1], OR(@cell = "Yes", @cell = "No"))

    After you apply the formulas to your Sheet Summary fields, you will need to change the fields to be formatted as Percentage. To do that, click the 3 dots next to the Summary field name, select "Number Formatting", and then select "Percentage".

    Hope this helps!

    -Ray

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭

    My method above will also ignore any blanks because it's strictly looking for Yes or No values.

  • This was exactly what I was looking to do!! Thank you!!

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭

    Awesome, glad it worked! Please remember to smash that Answer button, so others can more easily find solutions like this!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!