How to sum Child rows if they meet a two criteria

Lucas Rayala

@Benny Velazquez, wasn't that basically what I did, except I had the two blocks of formula swapped? In the example screenshot, take Ponce Harbor. You want the sum of all the "I" and "CO" rows (i.e. the Total Contract Liability, already created), subtracted from the sum of all the "D" and "F" rows (this is the same formula as Total Contract Liability, except replace "I" and "CO" with "D" and "F"). Here, I'll swap the formulas and get rid of the spacing.

=(IF(COUNT(CHILDREN(["I - Initial contract CO - Change Order D - Draw]@row)) = 0, "", SUMIF(CHILDREN(["I - Initial contract CO - Change Order D - Draw]@row), OR(@cell= "D", @cell = "F"), CHILDREN([Amount of Action]@row))) + IF(COUNT(CHILDREN(["I - Initial contract CO - Change Order D - Draw]@row)) = 0, "", IF(OR(["I - Initial contract CO - Change Order D - Draw]@row= "D", ["I - Initial contract CO - Change Order D - Draw]@row="F"), [Amount of Action]@row,"")) ) - (IF(COUNT(CHILDREN(["I - Initial contract CO - Change Order D - Draw]@row)) = 0, "", SUMIF(CHILDREN(["I - Initial contract CO - Change Order D - Draw]@row), OR(@cell= "I", @cell = "CO"), CHILDREN([Amount of Action]@row))) + IF(COUNT(CHILDREN(["I - Initial contract CO - Change Order D - Draw]@row)) = 0, "", IF(OR(["I - Initial contract CO - Change Order D - Draw]@row= "I", ["I - Initial contract CO - Change Order D - Draw]@row="CO"), [Amount of Action]@row,"")))

Benny Velazquez

@Lucas Rayala,

I've copy paste as well typed in the formula and it gave me unparseable, see my sample below.

Lucas Rayala

one thing you should do when you're working on complicated formulas is to temporarily change the column names for as long as you're working to something simpler to view. however, a quick look through your formula, there's a few things missing that were included in my example -- I circled them in the below screenshot -- the first is a closed parend, and the second is a missing set of quotes to indicated a blank for the "IF FALSE, THEN...". updates like that can waterfall. see if you can update. if not, try copying my formula again--I still think it should work. if that doesn't work, copy the working formula in total contract liability, put that in a helper column called "Helper", and update just the "I" and "CO" to "D" and "F". make sure that returns correctly. THEN, in Balance Due column, put this simple formula: =[Helper]@row - [Total Contract Liability]@row

Benny Velazquez

@Lucas Rayala, I've try several ways to do this and for some reason is not working for me, sorry for my lack of knowledge on this. I have one question for you? since this is only my test sheet, can I share it with you as an admin and see if you can ad this formula? hopefully I am not asking for to much. I've added the "Helper" column but its not calculating the entire column. I do appreciate all of your help on this, if this is not possible its ok. I do have a meeting schedule with the help desk from Smartsheet on Friday and i can bring this subject.

Lucas Rayala

Sure, send me a message and I'll shoot my email over for you to provide admin access.

Benny Velazquez

HAA @Lucas Rayala, I don't know how to send a message through here, maybe you send me one and I will respond. 😓

Lucas Rayala

Got it, i'll take a look.

Benny Velazquez

@Lucas Rayala, please delete. lol Thank you!

Lucas Rayala

@Benny Velazquez you should be set -- let me know if it looks right. I used the same equations I provided you previously but something went a little funny so I wrapped both equations in a "VALUE" wrapper, which converts them to numbers -- sometimes that helps to fix issues like that.

Benny Velazquez

@Lucas Rayala, You are my hero, this new formula did what I was looking for.

=IF(LEN([Total Contract Liability]@row) = 0, "", VALUE(IF(COUNT(CHILDREN(["I]@row)) = 0, "", SUMIF(CHILDREN(["I]@row), OR(@cell = "F", @cell = "D"), CHILDREN(Action@row))) + IF(COUNT(CHILDREN(["I]@row)) = 0, "", IF(OR(["I]@row = "F", ["I]@row = "D"), Action@row, ""))) - VALUE(IF(COUNT(CHILDREN(["I]@row)) = 0, "", SUMIF(CHILDREN(["I]@row), OR(@cell = "I", @cell = "CO"), CHILDREN(Action@row))) + IF(COUNT(CHILDREN(["I]@row)) = 0, "", IF(OR(["I]@row = "I", ["I]@row = "CO"), Action@row, ""))))

I am in debt with you, Thank you Thank you! 🤩

Lucas Rayala

no problem @Benny Velazquez, glad we got it worked out!