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

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:

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)

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?


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
Categories
Check out the Formula Handbook template!