Accessing Children of Children if it meets certain Conditions

Options

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!!!


Best Answer

«1

Answers

  • Charles Huenergardt
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Options

    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.
    Genevieve P. Employee Admin
    edited 02/05/20
    Options

    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
    Options

    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.
    Genevieve P. Employee Admin
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    edited 02/05/20
    Options

    @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
    Paul Newcome ✭✭✭✭✭✭
    Options

    @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
    Options

    @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.
    Genevieve P. Employee Admin
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    @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
    Paul Newcome ✭✭✭✭✭✭
    edited 02/05/20
    Options

    @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
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!