IF Statements and Summing Children
Hi there  I need a hand with a formula. I'm trying to do the following:
Show the difference between Budgeted Amount and Quote Amount unless there is a value in the Actual Amount column, then I'd like to show the difference between the Budgeted Amount and the Actual Amount. But I only want to calculate those values if the Quote Amount AND the Actual Amount aren't blank. If Actual Amount and Quote Amount are blank, the Budget Over/Under Column should be "0". And the formula should calculate the difference if it's a child, otherwise, it should show the sum of the children.
I'm so close but it seems that the IF/OR statement has thrown me off the trail. Could someone please lend a hand?
Here's the formula that worked prior to me realizing I only wanted the formula to run if both Quote Amount and Actual Amount were blank:
=IF([Actual Amount]@row > 1, IF(COUNT(CHILDREN()) = 0, [Actual Amount]@row  [Budgeted Amount]@row, SUM(CHILDREN())), IF(COUNT(CHILDREN()) = 0, [Quote Amount]@row  [Budgeted Amount]@row, SUM(CHILDREN())))
And here's a screen shot. The formula goes in the Budget Over/Under Column
Best Answer

I got you  use this:
=IF(COUNT(CHILDREN()) > 0, SUM(CHILDREN()), IF(AND(NOT(ISNUMBER([Quote Amount]@row)), NOT(ISNUMBER([Actual Amount]@row))), 0, IF(ISNUMBER([Actual Amount]@row), [Budgeted Amount]@row  [Actual Amount]@row, [Budgeted Amount]@row  [Quote Amount]@row)))
It ends up looking like this:
_____________________________________________________________________________________________
👨🏼💻 Dan Palenchar  School of Sheets Solutions Consulting  Smartsheet Aligned Gold Partner
If this response helped you please help me & the community by accepting it and reacting as you see fit (💡insightful, ⬆️ Vote Up, and/or ❤️Awesome).
🆘 Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus
▶️ Smartsheet Tutorial Videos: schoolofsheets.com/youtube
PS  If you have a follow up response attention use @Dan Palenchar so I get notified of your reply!
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!
Answers

I got you  use this:
=IF(COUNT(CHILDREN()) > 0, SUM(CHILDREN()), IF(AND(NOT(ISNUMBER([Quote Amount]@row)), NOT(ISNUMBER([Actual Amount]@row))), 0, IF(ISNUMBER([Actual Amount]@row), [Budgeted Amount]@row  [Actual Amount]@row, [Budgeted Amount]@row  [Quote Amount]@row)))
It ends up looking like this:
_____________________________________________________________________________________________
👨🏼💻 Dan Palenchar  School of Sheets Solutions Consulting  Smartsheet Aligned Gold Partner
If this response helped you please help me & the community by accepting it and reacting as you see fit (💡insightful, ⬆️ Vote Up, and/or ❤️Awesome).
🆘 Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus
▶️ Smartsheet Tutorial Videos: schoolofsheets.com/youtube
PS  If you have a follow up response attention use @Dan Palenchar so I get notified of your reply!
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!

@Dan Palenchar Thank you so much for your help! This worked like a charm.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.6K Get Help
 403 Global Discussions
 215 Industry Talk
 455 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 56 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 296 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!