Help writing an IF formula that sums all child rows only if they are greater than 0

Hi,

I'm trying to write a forumula that draws the total $ amount from a parent cell only if all of it's child rows are greater than 0, otherwise I want it to draw from a cell in another column.

Here is my formula so far but its returning INVALID OPERATION.

=IF(CHILDREN([Quote/PO Amount]@row) > 0, [Quote/PO Amount]@row, [Approved Budget]@row)


Is this possible? What am I doing wrong?

Answers

  • JGreenberg
    JGreenberg ✭✭
    edited 07/19/22

    ETA: Sorry...misread the question, but having trouble deleting this comment. Testing it out though.

    Here you go:

    =SUMIF(Children(), >0)

  • Hi thank you for your resonpnse. It's not a SUMIF formula that I'm after. I'm trying to pull a number from a column that sums up quotes but only IF all of it's child rows have been popluated (ie. the full project has been quoted). Otherwise I want the cell to draw the original Approved Budget amount.


    I'm not even sure this is possible.


    Here is a look at the spreadsheet:



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try something like this...


    =IF(COUNTIFS(CHILDREN([Quote/PO Amount]@row), @cell > 0) = COUNT(CHILDREN(Description@row), [Quote/PO Amount]@row, [Approved Budget]@row)

  • Thank you, wonderful human! You were misssing one parenthsis but i fixed it an it works! I've been trying to puzzle this out for weeks. Next time I am positing questions here a lot sooner :)

    Here is what worked:

    =IF(COUNTIFS(CHILDREN([Quote/PO Amount]@row), @cell > 0) = COUNT(CHILDREN(DESCRIPTION@row)), [Quote/PO Amount]@row, [Approved Budget]@row)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️


    As many times as I write out a COUNT/CHILDREN, I almost always miss that second closing parenthesis after the cell reference. 🤦‍♂️ Hahaha. Glad you caught it.

  • Now that I know this is possible.... Is there a way to work in a third column? A formula that says, "Pull the number from the invoiced column if all its child rows are greater than 0. If the child rows in the invoice column are less than 0 but the child rows in the quoted column are greater than 0, then pull from the quoted column. If neither are true, then pull from the approved budget column?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would use a nested IF with the same COUNTIFS = COUNT logic.


    =IF(COUNTIFS(CHILDREN([Invoiced Amount]@row), @cell > 0) = COUNT(CHILDREN(DESCRIPTION@row)), [Invoiced Amount]@row, IF(COUNTIFS(CHILDREN([Quote/PO Amount]@row), @cell > 0) = COUNT(CHILDREN(DESCRIPTION@row)), [Quote/PO Amount]@row, [Approved Budget]@row))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!