Formula Assistance (SUMIFS, COUNTIFS of CHILDREN)

CHealy

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?

L_123

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.

Paul Newcome

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.

CHealy

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. 

Paul Newcome

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

CHealy

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

 

 

 

Paul Newcome

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.

CHealy

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)

 

Paul Newcome

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?

CHealy

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. 

Paul Newcome

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

 

=IF(PARENT(Status@row) = "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?

CHealy

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

Paul Newcome

=IF(OR(PARENT(Status@row) = "Won", AND(COUNT(CHILDREN(Status@row)) = 0, Status@row = "Won")), 1)

 

Ok. Give this a shot. This looks for 2 different sets of criteria. 

1. If the Parent = "Won"

 

OR

 

2.a. No Children and

2.b. Status = "Won"

CHealy

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.

Paul Newcome

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

CHealy

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(Status@row) = "Won", AND(COUNT(CHILDREN(Status@row)) = 0, Status@row = "Won")), 1)