Formula Error

Jennifer Vaughn

I am trying to Sum up the parent rows and am getting an "Unparseable" from my formula. Here's the formula. Please let me know what I am doing wrong. I have tried 1 to 4 closing parentheses as well. Same thing. 

=SUM(PARENTS([ST Rev ($)]5:[ST Rev ($)]65)

 

Brian W

I'm not sure it can be done with a single formula. (Someone here with more knowledge may know how.) However, you could create a helper column that checks if a row has children:

=COUNT(CHILDREN([ST Rev ($)]@row))

And then sum the cells in your column where the parent column is greater than zero:

=SUMIF([Parent Column]5:[Parent Column]65, @cell > 0, ([ST Rev ($)]5:[ST Rev ($)]65))

Andrée Starå

Hi Jennifer,

Depending on your sheet structure the most straightforward way would probably be to add a parent at the top of the others sum all the children (the other parents you wanted to sum).

Would that work?

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

Both suggestions will work. In the case of Brian's though, you could do without the helper column and just include the criteria in a SUMIFS.

 

=SUMIFS([ST Rev ($)]5:[ST Rev ($)]65, [ST Rev ($)]5:[ST Rev ($)]65, COUNT(CHILDREN(@cell)) > 0)

 

The only problem you may run in to is if there is a row that has no children but is still on the same hierarchy level as the parent rows.

 

Row 1

     Row 2

     Row 3

Row 4

Row 5

     Row 6

     Row 7

 

In the case above, Row 4 would not be accounted for in the formula as there are no children rows.