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
Help Article Resources
Check out the Formula Handbook template!