Dashboards: Pie chart for yes vs no; Formula to count a column range with restrictions

SNickNBCUniUSH
edited 03/15/23 in Smartsheet Basics

Hello all,

I'm trying to create a pie chart like the one attached. Pie charts only consider numerical data, and since I'm looking to show a percent of things done ("Yes") vs not ("No"), I have to build a formula.

To give an idea of the breakdown, I attached a sheet visual.

I need restrict data in column one into just "Area 1".

I then need to select a range for column two: Zone 1, Zone 3, and Zone 5. Then I need to count the percent of "Yes" or "No" in columns 7, 8, and 9.

Lastly, I need to show the % yes in a pie chart for columns 7, 8, and 9, broken down by zone. Any help is greatly appreciated. Right now I only have a working formula for counting the % yes within the column:

=COUNTIF([Column 7]:[Column 7], "Yes") / COUNT([Column 7]:[Column 7])

Any help is greatly appreciated. Thanks!

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hey @SNickNBCUniUSH

    I would personally do this in a separate "Metrics" sheet, using cross-sheet formulas. You're on the right track!

    Here's how I would set up my sheet, based on your demo sheet (you don't have to colour code it):


    Then you can use two cross-sheet COUNTIFS per row to create your percentages.

    For example, for Zone 1, Column 7, it would look like this:

    =COUNTIFS({Area}, 1, {Zone}, 1, {Column 7}, "Yes") / COUNTIFS({Area}, 1, {Zone}, 1, {Column 7}, <>"")


    Then for each Zone you'd want to adjust the number you're looking for:

    {Zone}, 2,


    And then for each Column you'll need to delete out and create a new {column range}, like so:

    =COUNTIFS({Area}, 1, {Zone}, 1, {Column 8}, "Yes") / COUNTIFS({Area}, 1, {Zone}, 1, {Column 8}, <>"")



    Keep in mind once you've created the {range} you can simply copy/paste the text in the other formulas and they'll reference that same column as well. See: Create cross sheet references to work with data in another sheet

    Let me know if that makes sense and will work for you!

    Cheers,

    Genevieve

  • @Genevieve P.

    That makes a lot of sense! I'm going to try that. Appreciate the help!