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

SNickNBCUniUSH

Pie Chart.png

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.

Demo sheet.png

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!

Genevieve P.

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