Formula Assistance (SUMIFS, COUNTIFS of CHILDREN)

Paul Newcome

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

 

See how this works for you...

CHealy

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.

Paul Newcome

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?

CHealy

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.

Paul Newcome

{Completed Estimates Parent Row}

 

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

CHealy

That was exactly what it was - I'll need to review because the Parent Row checkbox column was not checked!

Paul Newcome

Hopefully its an easy fix for you. Feel free to ask away if you need anymore assistance.

CHealy

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)

Paul Newcome

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.

CHealy

I'm still getting the UNPARSEABLE error.

Paul Newcome

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.