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

Tara Factor

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?

JGreenberg

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

Here you go:

=SUMIF(Children(), >0)

Tara Factor

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

Try something like this...

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

Tara Factor

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

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.

Tara Factor

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?

Tara Factor

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