SUMIFS ISBLANK/NOT IS BLANK CHILDREN ROWS

Hi! I am trying to sum children rows if the date at column "MUNIS LEVEL 5" is either filled out (NOT ISBLANK) or not filled out (ISBLANK). I am getting an error. Is it a syntax issue? Thank you for your assistance!


Answers

  • MCorbin
    MCorbin Overachievers Alumni

    For a date column, use ISDATE rather than ISBLANK

  • Thank you @MCorbin the formula works perfectly when we manually select the range; however, when we try to setup the formula for the parent row to include all the children we are getting an error message. We are trying to sum all the Total Owe-Live children column when the Munis Approval Level 5 Date children is filled out and also when it's blank. Thank you in advance for your assistance!


  • MCorbin
    MCorbin Overachievers Alumni

    (sorry for the delay responding)

    It should definitely be do-able.

    I created a Helper column called "Parent". The Formula in that column is: =PARENT([Vendor / Budget]@row)

    Then my Total Formula becomes: =SUMIFS([Total Owe - Live]:[Total Owe - Live], [Munis Approval Level 5 Date]:[Munis Approval Level 5 Date], ISDATE(@cell), Parent:Parent, [Vendor / Budget]@row)

    And to capture the ones where there is no MUNIS date:

    =SUMIFS([Total Owe - Live]:[Total Owe - Live], [Munis Approval Level 5 Date]:[Munis Approval Level 5 Date], ISBLANK(@cell), Parent:Parent, [Vendor / Budget]@row)


  • Hi @MCorbin, no need to apologize thank you for your assistance with this we greatly appreciate it. We tried the following formula:

    =SUMIFS(CHILDREN([TOTAL OWE - LIVE]:[TOTAL OWE - LIVE]), CHILDREN([MUNIS APPROVAL LEVEL 5 DATE]:[MUNIS APPROVAL LEVEL 5 DATE]), ISDATE(@cell))

    And it was successful. When we applied it to row "TOTAL OWE-LIVE" a "#CIRCULAR REFERENCE" was generated...

    =SUMIFS(CHILDREN([TOTAL OWE - LIVE]:[TOTAL OWE - LIVE]), CHILDREN([MUNIS APPROVAL LEVEL 5 DATE]:[MUNIS APPROVAL LEVEL 5 DATE]), ISBLANK(@cell))

    Can this be resolved without having to create the additional "Parent:Parent" row?


    THANK YOU!!!!😊

  • MCorbin
    MCorbin Overachievers Alumni

    If you tried to move the formula into the "TOTAL OWE-LIVE" column, you're right, it will create a circular reference (because that's the column you're trying to add up).

    Your best bet is to put your formulas in 2 new columns. They can't reside in the same column you're using in the formulas.

  • David Joyeuse
    David Joyeuse ✭✭✭✭✭

    Hi @Lily Alvarez

    You got your range wrong in your sumif formula when dealing with Children.

    =SUMIFS(CHILDREN([TOTAL OWE - LIVE]:[TOTAL OWE - LIVE]), CHILDREN([MUNIS APPROVAL LEVEL 5 DATE]:[MUNIS APPROVAL LEVEL 5 DATE]), ISDATE(@cell))

    When referencing CHILDREN, keep in mind that you want to deal with CHILDREN of a specific row, not a full column. Also, you do not need to specify the row/column if you want to add CHILDREN directly beneath the cell where you're putting the formula.

    So you should have this:

    =SUMIFS(CHILDREN(), CHILDREN([MUNIS APPROVAL LEVEL 5 DATE]@row), ISDATE(@cell))

    Hope it helped!

  • Great Afternoon @MCorbin,

    I have tried your formula with the helper column on the PO Liquidated parent row and I keep getting the UPARSEABLE error.

    Your assistance is greatly appreciated. Thank you in advance!!!



  • MCorbin
    MCorbin Overachievers Alumni

    An "Unparseable" error usually means you have a comma or parenthesis in the wrong place.


    In this case, it's right after "@cell" - you have 2 close parentheses there, you only need one:


    =SUMIFS([Total Owe - Live]:[Total Owe - Live], [Munis Approval Level 5 Date]:[Munis Approval Level 5 Date], ISDATE(@cell), Parent:Parent, [Vendor / WO#]@row)

  • THANK YOU @MCorbin It worked! :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!