Accessing Children of Children if it meets certain Conditions

Charles Huenergardt

Hi,

I'm having trouble getting a formula to help me tally info for a dashboard. I have a sheet that is tracking the requirements of several capabilities of a system that we're migrating. The Column called Core Capabilities, has 4 children (Categories); Critical, High, Medium, Low. Each of those categories has a range of children (Requirements) that start at 0 and go on.

I'm trying to capture in a formula how many total Criticals, Highs, Mediums, and Lows I have. I'm also trying to capture the total status in each of these categories (Red, Yellow, Green which is tracked in a separate Status Column)

I mocked up an example of what my requirement tracking sheet looks like. There are many more Core Capabilities in the actual sheet. And the Status is actually an output of the action in several columns to the right. I appreciate any help!!!

Genevieve P.

Hi Charles,

I wasn't quite sure if you meant how many each individual status has, or how many one status as an overall total... I've replicated your sheet with some examples of what I might do in this situation.

First, I added a bit of colour using Conditional Formatting just to help me categorize the different sections. (You can ignore this, but just to show you what I did, see below.)

Then I set up a Helper Column called "Category Totals" that uses a COUNT(CHILDREN formula to count each category's child rows:

=COUNT(CHILDREN([Core Capabilities]@row))

This is put in the Parent Row.

Finally, I used a SUMIF formula with CONTAINS in a Sheet Summary field to total up all of the different totals for each category:

=SUMIF([Core Capabilities]:[Core Capabilities], CONTAINS("Critical", @cell), [Category Totals]:[Category Totals])

You can then use any of these cells, including Sheet Summary cells, in a Dashboard. If you didn't need the helper column in the sheet that counts the children, you can hide this on your sheet.

In regards to your second question with the Status colours, could you explain exactly what it is you're looking to count? Are you wanting to know the total number of Reds in the entire sheet, or per individual Capability/Category (like the helper row for the Category totals), or per each Category (like the Sheet Summary totals)?

Let me know if this will work for you!

Cheers,

Genevieve

Genevieve P.

Hi Charles,

I wasn't quite sure if you meant how many each individual status has, or how many one status as an overall total... I've replicated your sheet with some examples of what I might do in this situation.

First, I added a bit of colour using Conditional Formatting just to help me categorize the different sections. (You can ignore this, but just to show you what I did, see below.)

Then I set up a Helper Column called "Category Totals" that uses a COUNT(CHILDREN formula to count each category's child rows:

=COUNT(CHILDREN([Core Capabilities]@row))

This is put in the Parent Row.

Finally, I used a SUMIF formula with CONTAINS in a Sheet Summary field to total up all of the different totals for each category:

=SUMIF([Core Capabilities]:[Core Capabilities], CONTAINS("Critical", @cell), [Category Totals]:[Category Totals])

You can then use any of these cells, including Sheet Summary cells, in a Dashboard. If you didn't need the helper column in the sheet that counts the children, you can hide this on your sheet.

In regards to your second question with the Status colours, could you explain exactly what it is you're looking to count? Are you wanting to know the total number of Reds in the entire sheet, or per individual Capability/Category (like the helper row for the Category totals), or per each Category (like the Sheet Summary totals)?

Let me know if this will work for you!

Cheers,

Genevieve

Charles Huenergardt

Genevieve, I learned so much from this comment.

Essentially, I'm trying to calculate how many critical, high, medium, and low requirements we have and whether or not we have met those requirements (Red = Not Met, Green = Met, Yellow = Waived, no longer a requirement)

I want to communicate this info easily and visually to execs, so I'd like to do that in a dashboard view. My understanding is that I cannot do calculations in a dashboard, so I have a separate sheet that computes what I need to communicate, and then I will have the dashboard refer to that. If you have an easier way to go about this, I'd love to learn.

I appreciate the help!

~C~

Paul Newcome

To start, you will want to add in a column that will differentiate between children and parent rows since hierarchy functions cannot be used in cross sheet references.

Then use can use COUNTIFS functions to count each of the colors that have this additional column checked.

The trick is figuring out how to check the box for exactly which rows you want to use in your calculations. Is it based on being in the third tier (grandchildren rows) or is it based on not having any children rows themselves? These two will actually provide different results, so figuring out that part is key.

Charles Huenergardt

What I did was create another column called Priority and then in that column a formula looks to see what it's parent is, then reports that as text in the column. Then in the calculating sheets I use COUNTIFS to grab the priority and the status. Ultimately, I'll hide the Priority column when we start tracking.

Genevieve P.

Woah sounds great! Glad you figured something out 😊

If the end goal is for showing this data in a Dashboard... I actually wonder if you might want to look into building different Reports, as well.

You could have the total numbers listed in your Dashboard, and then below it embed a few Report widgets (or published links of the reports, depending on what sharing permissions you want to give), that actually show the line items relating to the most important totals.

Charles Huenergardt

I'm trying to use the sheet summary feature instead of a separate sheet for the calculations. When I input this...

=COUNTIFS(Priority:Priority, "Critical", Status:Status, "Red")

I get a Divide By Zero error. Which makes no sense to me. Is that a bug, or am I doing it wrong?

Thanks!

Genevieve P.

Ahhhh that makes no sense to me either... I just tested the same formula and it works fine on my sheet/sheet summary field.

Can you provide a screen capture with the formula open in the Summary field & the grid view showing the two columns? (Blocking out any sensitive data.) What are your column types, as well?

Paul Newcome

If I remember correctly, I believe that is an error that will pull through other calculations (instead of triggering a "BLOCKED" error). Is it possible that error could be present in any cells within your ranges?

Charles Huenergardt

@Genevieve P Picture is attached. The Priority is a Text/Number column. The Status is a Symbol (although I have formulas in the categories and the parent capabilities that are a number, which I base conditional formatting on.)

@Paul Newcome There isn't an error in the calculations in the ranges that it's looking for. I'm actually referencing those ranges from another sheet and the COUNTIFS works fine.

Paul Newcome

@Charles Huenergardt In the other COUNTIFS referencing those ranges, is it the same exact formula?

If both are referencing the Priority Column first but the other is referencing something other than "Critical" then it could be that the error is in the Status column on a row containing "Critical" in the Priority column.

Not to say that there is definitely an error. It very well could be a bug. Just trying to go through some trouble shooting. Based on the syntax, an error in the Status column would not break your COUNTIFS unless that row first met the "Critical" criteria. If your other COUNTIFS is looking at a different criteria for the Priority column, it will completely skip over the error and work just fine. Just something to keep in mind...

Charles Huenergardt

@Paul Newcome In my other "calculator" sheet, this is the COUNTIFS formula. They do seem to be identical, but I've been looking at it for so long the pixels are melting together, so I appreciate the sanity check.

=COUNTIFS({Requirement Priority}, "Critical", {Requirement Status}, "Red")

Genevieve P.

I'm getting a #BLOCKED message when there's an error in one of the columns, instead of the Divide By Zero.

Could you maybe save the sheet, log out & in again, and see if it's still happening? From what I can tell your syntax and columns are all set up properly. That said, there are a lot of collapsed rows that might be causing the issue... but not if your cross-sheet formula is working as expected.

If logging in/out again didn't help, it might be time to Contact Support. 😬

Paul Newcome

@Genevieve P and @Charles Huenergardt

I put together a quick test where I intentionally generated a #DIVIDE BY ZERO error and then referenced it in another formula. The error pulled through instead of generating the #BLOCKED error. I also duplicated the same formulas referencing numbers that wouldn't generate an error to show where it works without the initial error.

One thing I did find I was mistaken about though... I used a basic COUNTIFS to count how many in the Number column equaled 1 and the Letter column was not blank. I set it up in that order meaning it pulled the rows containing 1 first and still got the #DIVIDE BY ZERO error. So my earlier theory about why one would work and the other not is actually incorrect.

If the other formula using the cross sheet reference is still working and the one in the Sheet Summary is not, then I second Genevieve's suggestion of reaching out to Support (link included).

Paul Newcome

@Charles Huenergardt

You can actually select the column header and use CTRL+F to search for # and it will jump you to any cells that have an error (if there are any).

You can also create a filter that mimics your formula to look at all of the rows that SHOULD be getting pulled.

Charles Huenergardt

Good to know. Thanks for the help! I've contacted support, told them the issue and pointed them to this thread for the details.