How to sum Child rows if they meet a two criteria

Benny Velazquez

Hello,

I've been searching and hitting a dead end on this formula, I am trying to sumifs a cell with two criteria. When I use this formula =SUMIF(["I - Initial contract CO - Change Order D - Draw]1:["I - Initial contract CO - Change Order D - Draw]5, "I", [Amount of Action]1:[Amount of Action]5) it does work for that criteria.

But when I use =SUMIFS([Amount of Action]1:[Amount of Action]5, ["I - Initial contract CO - Change Order D - Draw]1:["I - Initial contract CO - Change Order D - Draw]5, "I", [Amount of Action]1:[Amount of Action]5, ["I - Initial contract CO - Change Order D - Draw]1:["I - Initial contract CO - Change Order D - Draw]5, "CO") it gives me an incorrect argument set. We want to be able to sum the parent and child row on each separate contract as shown on this picture below, but only the ones with "I" and "CO". That will give us the total liability.

Hopefully this is not confusing, please help.

Lucas Rayala

Oh, sorry @Benny Velazquez, you want it to sum if it meets EITHER of the two criteria? Meaning, if either of these are true, then sum? I guess I missed that you were referencing the same column in both your conditions. To do that:

=SUMIFS(

[Amount of Action]1:[Amount of Action]5,

["I - Initial contract CO - Change Order D - Draw]1:["I - Initial contract CO - Change Order D - Draw]5, OR(@cell= "I", @cell = "CO"))

Lucas Rayala

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

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.

Lucas Rayala

Remove the second ", [Amount of Action]1:[Amount of Action]5" from the statement -- that's causing your error.

=SUMIFS(

[Amount of Action]1:[Amount of Action]5,

["I - Initial contract CO - Change Order D - Draw]1:["I - Initial contract CO - Change Order D - Draw]5, "I",

["I - Initial contract CO - Change Order D - Draw]1:["I - Initial contract CO - Change Order D - Draw]5, "CO")

Benny Velazquez

Hi Lucas,

Thank you so much for your response. I've followed your instructions and now it leaves the argument with o.oo value.

Lucas Rayala

Oh, sorry @Benny Velazquez, you want it to sum if it meets EITHER of the two criteria? Meaning, if either of these are true, then sum? I guess I missed that you were referencing the same column in both your conditions. To do that:

=SUMIFS(

[Amount of Action]1:[Amount of Action]5,

["I - Initial contract CO - Change Order D - Draw]1:["I - Initial contract CO - Change Order D - Draw]5, OR(@cell= "I", @cell = "CO"))

Benny Velazquez

Lucas,

This definitely worked, I was way off all along. Thank you so much for your time and support on this subject.

Have a great day!

Benny Velazquez

Lucas,

How would you convert this column formula where it calculates each contract separate rather than the hole entire sheet. All of these contracts are for different properties and I've separate them by indenting, is this even possible or do I just need to add the formula on each cell.

Lucas Rayala

Hi @Benny Velazquez, you can make a column formula that uses parent/child relationships to sum what you need. Try this:

=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)))

Benny Velazquez

@Lucas Rayala, I've added as suggested and it gave me a "0" value.

Lucas Rayala

@Benny Velazquez, you missed the parenthesis after the "CO" and have an extra one at the end. You should be able to just copy/paste my code.

=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)))

Benny Velazquez

@Lucas Rayala, the formula did work but it is not adding up the total correctly, it is missing the parent cell. for example, "I" and "CO" should all be sum together. on the total liability it should be a total of $55,055.61, so its only counting the children's.

Sorry for the repeated requests, I am a beginner in Smartsheet and did not had many experience in Excel.

Lucas Rayala

Haaa.... funny. @Benny Velazquez, this could be done more elegantly, I'm sure, but this should work:

=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, That did it, you are awesome. I would've never figured this one on my own, I've tried for several days with no luck. thank you so much for your help on this.😍

Lucas Rayala

@Benny Velazquez glad I could help!

Benny Velazquez

@Lucas Rayala, 😁 I have one last request on this subject.

Now that you help me solved the issue with the total liability, I would like to the the same at balance due. I would like to add the same formula but only to see on initial contract CO the "D" and "F" criteria and deduct from the total contract liability. I've use your formula but instead, I've added the last column.

=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= "D", ["I - Initial contract CO - Change Order D - Draw]@row="F"), [Amount of Action]@row,-[Total Contract Liability]@row""))

Did not work of course, so how should it be?

Lucas Rayala

Hi! Good job digging into the formula. One thing to think about: you can have multiple formulas in a single line, and if they all have numbers as outputs, you can add or subtract them. Use parenthesis to make sure everything is adding and subtracting appropriately. So take the first formula and repeat it, updating the values you want to update in the repeated version. I've spaced it so you can see -- notice I've got a minus sign between the two formulas. I'm not actually sure what you're looking for, but maybe this will get you close:

=

(

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,""))

)

-

(

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,""))

)

What I thought you were asking for was to subtract the summed "D" and "F" criteria from the summed "I" and "CO" critieria -- that's what the above will do.

Benny Velazquez

@Lucas Rayala,

Sorry for not being to specific, what I want to do is to be able to subtract the total liability from my inputs coming from the initial contract column. "D" stands for Draw Request and "F" for Final invoice submitted, all of these amount will be deducted from the total liability. The new formula will go in the Balance Due column similar to this one, but instead will have the "D" and "F" minus the total liability.

=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,""))

I hope this is not confusing you.