Dashboard Help- COUNTIFS, Checkboxes and Hierarchy

megtro610

I am trying to make a widget for a dynamic dashboard that shows how many child level 2s and if the "Closed?" box is checked or not. I would like to make it a pie chart if possible.

The level column is measuring by Parent/child hierarchy with the formula

=COUNT(ANCESTORS()) + 1

Thanks!

AKnight

@megtro610

There are two ways to do this, the formula illustrated by @AngB789 above, or if you are uncomfortable with formulas, you can also always set up a report that pulls from this sheet like so:

1. Generate new row report (I usually have mine in a folder labeled "Charts"
2. Select this sheet as the source sheet
3. Select the "Closed?" column in columns
4. Filter the "Level" column to equals 2
5. Group the report by "Closed?" and collapse the group (bottom left of the group dropdown)
6. Count the "Closed?" column under summary
7. Select this report when generating a new pie chart

This option is also better if you want to select the pie chart (option when clicked open source), and see what row falls in each category of the pie chart; however if you are comfortable with formulas and don't really care about what row pertains to the pie wedge, I suggest going with AngB's way and create a collapsible summary row at the top for charts.

Edit to add as I just saw your additional comment, you can add/change the filters to make sure it contains only rows that are under specific primary columns (whether that is determined by an additional column with dropdown tags or if primary CONTAINS certain phrases)

AngB789

@megtro610 Try this formula if you are having issues with that. If you are looking at counting all them I would suggest copying what you do for level 2 and do an open and closed for each level. Then when you go to add the pie chart you would highlight all the metrics that you want in that particular chart.

For closed:

=COUNTIFS([Level]:[Level], "2", [Closed?]:[Closed?], 1)

For Open:

=COUNTIFS([Level]:[Level], "2", [Closed?]:[Closed?], 0)

See below screen show on how I set up the information for pie chart. You have to have a column for the name of what you are trying to compare to:

AKnight

Hey @megtro610 !

Just for clarification, would the pie chart display the number of level 2s that are closed vs not closed? Or will the pie chart show the count of each level that is marked as closed?

megtro610

@AKnight It would be for how many are not closed.

megtro610

@AngB789 Thank you! Is there a way to reference only a certain number of rows? For example, I want to make a pie for each unchecked level 2 there is under a specific level 1 row. I hope my question makes sense 😊

AngB789

@megtro610 Can you show me what you are referring to? What do you want to track and where do you want it to stop?

AKnight

@megtro610

There are two ways to do this, the formula illustrated by @AngB789 above, or if you are uncomfortable with formulas, you can also always set up a report that pulls from this sheet like so:

1. Generate new row report (I usually have mine in a folder labeled "Charts"
2. Select this sheet as the source sheet
3. Select the "Closed?" column in columns
4. Filter the "Level" column to equals 2
5. Group the report by "Closed?" and collapse the group (bottom left of the group dropdown)
6. Count the "Closed?" column under summary
7. Select this report when generating a new pie chart

This option is also better if you want to select the pie chart (option when clicked open source), and see what row falls in each category of the pie chart; however if you are comfortable with formulas and don't really care about what row pertains to the pie wedge, I suggest going with AngB's way and create a collapsible summary row at the top for charts.

Edit to add as I just saw your additional comment, you can add/change the filters to make sure it contains only rows that are under specific primary columns (whether that is determined by an additional column with dropdown tags or if primary CONTAINS certain phrases)

megtro610

AKnight

Mar 4, 2025

@megtro610

There are two ways to do this, the formula illustrated by @AngB789 above, or if you are uncomfortable with formulas, you can also always set up a report that pulls from this sheet like so:

1. Generate new row report (I usually have mine in a folder labeled "Charts"
2. Select this sheet as the source sheet
3. Select the "Closed?" column in columns
4. Filter the "Level" column to equals 2
5. Group the report by "Closed?" and collapse the group (bottom left of the group dropdown)
6. Count the "Closed?" column under summary
7. Select this report when generating a new pie chart

This option is also better if you want to select the pie chart (option when clicked open source), and see what row falls in each category of the pie chart; however if you are comfortable with formulas and don't really care about what row pertains to the pie wedge, I suggest going with AngB's way and create a collapsible summary row at the top for charts.

Edit to add as I just saw your additional comment, you can add/change the filters to make sure it contains only rows that are under specific primary columns (whether that is determined by an additional column with dropdown tags or if primary CONTAINS certain phrases)

Thank you! I did not realize you could just make a report rather than complicated formulas to do the work (like I've seen on the Youtube videos that I've been using). This makes my life so much easier 😁