26

I'm having difficulty with a few formulas wherein I'm attempting to SUM and COUNT only the CHILD rows so as to not duplicate amounts or counts (ie. need to exclude the Parent Row). 

The formula below works when the BOLD text is not inserted.

The issue is that the potential exists where the sum and/or count will include the Parent Row. 

I’m trying to find a workaround for that, for both SUMIFS and COUNTIFS. 

=COUNTIFS(CHILDREN({TEAM Completed Estimates Status}, "Won", {TEAM Completed Estimates Estimator}, FIND("John Smith", @cell)>0))

=SUMIFS(CHILDREN({TEAM Completed Estimates Project Sell Price}, {TEAM Completed Estimates Status}, "Won", {TEAM Completed Estimates Estimator}, FIND("John Smith", @cell)>0))

Can you assist?

Functionality

Comments

weird that 2 people asked near the same question in about 5 minutes hahaha

 

anyway CHILDREN() doesn't work across sheets. Your best bet is to use the COLLECT formula to return a set of values based on a criteria, then analyze that.

In reply to by [email protected]

I've noticed that questions tend to come in waves. It's weird. All of the sudden you'll start seeing a bunch of people asking for the same thing, then just as suddenly, it stops. There was one I must have answered half a dozen times in 2 weeks, but then I haven't seen it come up since.

 

As for the actual question here...

 

I tend to use a helper column to set which rows I want to pull from automated by a formula that counts the children and checks a box depending on whether or not it is a parent row. I then use a COUNTIFS or SUMIFS and use that checkbox column as a criteria range/criteria in my x-sheet formulas.

In reply to by Paul Newcome

I do have a column that checks a box where the row is a parent row.

So I have a Parent Row (that represents a complete project) that sums the Children Rows (individual systems). In order to track for example the dollar amount "quoted" and "won" in a particular region if only one or two systems (Child Row) is marked "won" I would have to enter the region on those lines.  The region is also marked on the Parent Row therefore the dollar amount "quoted" is being duplicated for those two systems.  I'm thinking then, by using the checkbox, there is a way to potentially exclude the child rows from the "amount quoted" formula. I'm just not sure how to go about that. 

=SUMIFS({TEAM Completed Estimates Project Sell Price}, {TEAM Completed Estimates Status}, "Won", {TEAM Completed Estimates Estimator}, FIND("John Smith", @cell)>0, {TEAM Completed Estimates Parent Checkbox Column}, @cell = 1)

 

If you already have a column designating the parent rows, then you can just add the BOLD UNDERLINED portion above to only SUM the parent rows.

=SUMIFS({TEAM Completed Estimates Project Sell Price}, {TEAM Completed Estimates Status}, "Won", {TEAM Completed Estimates Estimator}, FIND("John Smith", @cell)>0, {TEAM Completed Estimates Parent Checkbox Column}, @cell = 1)

If you already have a column designating the parent rows, then you can just add the BOLD UNDERLINED portion above to only SUM the parent rows.

 

This really isn't returning what I'm looking for.  I don't want to sum or count the Parent row in this particular instance.

We have a Project (Parent Row) that may include several systems (Children).

If the Parent Row is marked "Awarded" for example, I need a formula to count

the number of "System 1" (Child Row) which is awarded as a result of the Parent Row being marked such.  Likewise, to sum the dollar amount of "System 1" that wes awarded (again, where that status is only marked in the Parent Row). 

 

 

 

So you want to sum and count the children and not the parents?

 

You would change the = 1 to = 0.

 

Are you able to post some screenshots? That would help immensely.

In reply to by Paul Newcome

Uploading a screenshot of an example via excel.

The formula I have as it stands so far is

=COUNTIFS({Completed Estimates Status}, "Won", {Completed Estimates Project Type},

FIND("Fire Alarm", @cell) > 0)

 

Are you just trying to pull that $5,000.00 or sum all of the children because company abc is marked as won and one of the children is marked as fire alarm?

  1. I’m attempting to pull any and all projects that are marked Fire Alarm. In this example, just the $5K but if there are a total of 50 projects and 10 include That system type I want to pull that count of 10 and the associated total dollar amount. 

Ah. Ok. I am going to suggest a helper checkbox column using a formula along the lines of...

 

=IF(PARENT([email protected]) = "Won", 1)

 

This would check your box if the parent row is marked as "Won". It would mark all of the children f that parent row, but you could then use your SUMIFS formula to look for that checkbox along with your specific text.

 

Are we getting closer to a working solution for you?

In reply to by Paul Newcome

Getting closer - that works however it only captures the child rows, it doesn't appear to be including those line items where there is only one system that would be on a line to itself (not parent/child).

Thanks Paul!  I think that's got me where I need to be.

I just need to run through a few more tests to be certain but so far looks good.

I appreciate all the help with this.

No worries. Happy to help. yes Let me know if you need any changes or whatnot.

How can I edit this formula if I did not want the Parent Row to get checked within the "Helper checkbox column"?

                  

=IF(OR(PARENT([email protected]) = "Won", AND(COUNT(CHILDREN([email protected])) = 0, [email protected] = "Won")), 1)

I thought I had this formula up and running properly but every so often I do a review when something changes on the linked sheet and this morning, the dollar amount seems to be adding both the Parent and the Children amounts where I would only like the children to be included in the sum.

Here is the current formula:

=SUMIFS({Completed Estimates Project Sell Price}, {Completed Estimates Electrical Contractor}, FIND("ABC Company", @cell) > 0, {Completed Estimates Parent Row}, @cell = 0)

Up until this morning, I believed that the last section was excluding the Parent Row amount.

Did you double check the formula you are using to exclude parent rows? Has anything changed with it or does it appear to not be working?

In reply to by Paul Newcome

I would do "spot checks" and up until this morning I believed it was working correctly.

I guess I can take a smaller "sample" of the Completed Estimates and test.

{Completed Estimates Parent Row}

 

I am assuming this is a checkbox? What is the formula you are using to designate parent rows?

I am trying to sum specific ranges of rows, excluding the parent row from the total.

I have a formula that works on a reference sheet, (not specific to a range) that works but I am not having any luck attempting to adapt that to my purposes here.

=SUMIF(Status2:Status1183, "Won", [Project Sell Price]2:[Project Sell Price]1183), (Parent Row]2:Parent Row1183, @cell = 0

The formula that works is:

=SUMIFS({Completed Estimates Project Sell Price}, {Completed Estimates Status}, FIND("Won", @cell) > 0, {Completed Estimates Parent Row}, @cell = 0)

If you are trying to make the first formula work, you will need to move the closing parenthesis from after [Project Sell Price]1183 to the very end of the formula.

I'm still getting the UNPARSEABLE error.

In reply to by CHealy

Sorry. I just took a closer look and noticed more issues...

 

=SUMIFS(Status2:Status1183, "Won", [Project Sell Price]2:[Project Sell Price]1183, Parent Row]2:[Parent Row]1183, @cell = 0)

.

First you will need to use a SUMIFS since you have multiple sets of criteria range/criteria. Keep in mind that SUMIFS has a different syntax from SUMIF.

There is an extra opening parenthesis before the first Parent Row column name.

And you are also missing the square brackets around the second Parent Row column name.