SUMIFS ISBLANK/NOT IS BLANK CHILDREN ROWS

Lily Alvarez

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!

MCorbin

For a date column, use ISDATE rather than ISBLANK

Stephany Gonzales

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

(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)

Lily Alvarez

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

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

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!

Stephany Gonzales

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

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)

Stephany Gonzales

THANK YOU @MCorbin It worked! :)