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

Options

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?

• edited 07/19/22
Options

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

Here you go:

=SUMIF(Children(), >0)

• Options

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:

• ✭✭✭✭✭✭
Options

Try something like this...

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

• Options

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)

• ✭✭✭✭✭✭
Options

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.

• Options

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?

• Options
• ✭✭✭✭✭✭
Options

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!