Trying to figure out % a certain word was chosen from a drop down

I want to say what percent each office has of promoters in each section then in the entire sheet.

Thank you!!

Best Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Goodie3133

    Where do you want this percent to appear? In your screen capture it looks like you have Hierarchy with Parent rows, and you're looking to see the percent of the Children rows, is that correct?

    If so, you can place this formula in the Parent's cell for your Dropdown Column:

    =COUNTIF(CHILDREN(), "Promoter") / COUNT(CHILDREN())

    Then you can format the cell to be a % type of cell using the formatting toolbar at the top of the sheet. Let me know if this makes sense!

    Cheers,

    Genevieve

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Goodie3133

    You can set Conditional Formatting rules on that column in order to change the colour of the cell or the text based on the number it outputs. Keep in mind you'll want to set the criteria as a Decimal (ex. 80% is 0.8)

    See: Conditional Formatting

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Goodie3133

    Where do you want this percent to appear? In your screen capture it looks like you have Hierarchy with Parent rows, and you're looking to see the percent of the Children rows, is that correct?

    If so, you can place this formula in the Parent's cell for your Dropdown Column:

    =COUNTIF(CHILDREN(), "Promoter") / COUNT(CHILDREN())

    Then you can format the cell to be a % type of cell using the formatting toolbar at the top of the sheet. Let me know if this makes sense!

    Cheers,

    Genevieve

  • @Genevieve P.

    It worked! Thank you so much!!!!

  • No problem! I'm glad I could help 🙂

  • Goodie3133
    Goodie3133 ✭✭
    edited 11/18/21

    @Genevieve P. My goal for this sheet is to show Promoter status in the child account then have a status table on the top of each sheet to show where the total of each status is - in my mind something like this. And the percent from the sheet in light blue. Is that possible?

    I tried this formula on the parent line next to the one that worked for promoter earlier and tried to reference the same child columns to get a percent of the other options (Distractor, unknown, Passive) and I get #divide by zero.

    If you have a better way for me to demonstrate this I am also very open. THanks!


  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Goodie3133

    You can set Conditional Formatting rules on that column in order to change the colour of the cell or the text based on the number it outputs. Keep in mind you'll want to set the criteria as a Decimal (ex. 80% is 0.8)

    See: Conditional Formatting

  • Hi @Goodie3133

    My apologies! I didn't see that you had edited the comment from the original question.

    In this instance, you'll want to make sure you're referencing the one Parent cell inside of the Children function.

    Ex:

    CHILDREN([Distractor, Promoter, Passive, Unknown]@row)

    This says, look at the children from this cell.

    In your formula, you're selecting the actual children range, but the function will do that for you. Since you're referencing a child row that has no children in your formula, the COUNT is 0 which is why you're getting that error.

    Try this instead:

    =COUNTIF(CHILDREN([Distractor, Promoter, Passive, Unknown]@row), "Unknown") / COUNT(CHILDREN([Distractor, Promoter, Passive, Unknown]@row)

    Does that work and make sense?

    Cheers!

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!