SUMIFS using PARENT, CHILDREN, etc

jb@59069

Hello.

I have multiple sheets using formulas with SUMIFS statements to total expenditures based on date range, Service Line and Category. This has been working well for months:

=SUMIFS(Amount:Amount, [Date Charged]:[Date Charged], @cell > $[Date-Formula]$35, [Date Charged]:[Date Charged], @cell < $[Date-Formula]$38, [Service Line]:[Service Line], PARENT($Holder@row), Category:Category, $Holder@row)

However, we now need to add Project into the mix before/as the PARENT of Service Line, thus making Category the grandchild.

For the life of me, I cannot figure out how to make this 3-tier formula work.

Unknown

This content has been removed.

Genevieve P.

Hi @jb@59069

I'm not quite sure I understand the structure of the sheet. Would you be able to post a screen capture showing how Service Line, Category, and the column called Holder are set up in your sheet (but block out any sensitive data)? Is there a formula the Holder column?

You can use the ANCESTORS function to determine level of hierarchy, but how this function is used will depend on your sheet set up.

Thanks!

Genevieve

Unknown

This content has been removed.

Genevieve P.

Hi @jb@59069

Thank you for the clarification! If I'm understanding you correctly, this formula would be placed in the rows that are expanded and shown in Capture 1a (with 1b scrolled over to the right), is that correct?

If so, you actually don't need to adjust your formula at all. If you create another level above everything as a grandparent row, this won't affect the fact that your current row, the Category, is still a child of the Service Line row.

Here's a mock-up I created to try and understand the set up. Here you can see I have a summary grandparent line, but the formula isn't taking that into account for these Grandchild rows, because it's only concerned with its current row.

Please let me know if I misunderstood where the formula is being placed or what the issue is.

Cheers,

Genevieve

Unknown

This content has been removed.

Unknown

This content has been removed.

Genevieve P.

Hi @jb@59069

My apologies! I didn't receive a notification for your previous reply. Thank you for explaining a little further, and I think now I understand what you are looking to do. You're adding a new column along with another level of hierarchy and need the formula to look for the Grandparent row as a criteria.

Since there currently is no "grandparent" function, the way to get around this would be to create a helper column which identifies the parent for each child. This would bring the Grandparent name into the Parent's row, so that we can use it in our formula with the PARENT function, instead.

This would be the helper formula:

=IF(COUNT(ANCESTORS(Holder@row)) > 1, PARENT(Holder@row))

Then in your current formula, you would add in the additional project column as a new range to look into, with the criteria being that it equals the Parent of this helper column (in my sheet I've named this "Hierarchy"), which is actually the grandparent of the Holder column:

Project:Project, PARENT(Hierarchy@row)

Total formula:

=SUMIFS(Amount:Amount, [Date Charged]:[Date Charged], @cell > $[Date-Formula]$33, [Date Charged]:[Date Charged], @cell < $[Date-Formula]$36, [Service Line]:[Service Line], PARENT($Holder@row), Category:Category, $Holder@row, Project:Project, PARENT($Hierarchy@row))

Here's an example, with the grey column being the helper Hierarchy and the first formula:

Let me know if this works for you!

Cheers,

Genevieve

Unknown

This content has been removed.

Genevieve P.

Hi @jb@59069

Yes, you are correct, but I set up my example based on what I understood you would be implementing already : "we now need to add Project into the mix before/as the PARENT of Service Line, thus making Category the grandchild."

If there is a different way you are looking to format the sheet, then there may possibly be a different way to include this criteria into the SUMIF formula.

For example, you could also type in the specific criteria, based on each Project section:

=SUMIFS(Amount:Amount, [Date Charged]:[Date Charged], @cell > $[Date-Formula]$33, [Date Charged]:[Date Charged], @cell < $[Date-Formula]$36, [Service Line]:[Service Line], PARENT($Holder@row), Category:Category, $Holder@row, Project:Project, "Project 1")

I would suggest creating a copy of the sheet to test out the new formatting & formulas before implementing it on the current sheet.

Unknown

This content has been removed.

Unknown

This content has been removed.

Unknown

This content has been removed.

Unknown

This content has been removed.

Genevieve P.

Hi @jb@59069

It sounds like the premium add-on Pivot App would be a better solution for the types of calculations you are looking to do.

The solution above is based off of the initial question of how to include a grandparent row in a formula, versus if this is best practice for your process or sheet. If there is a way you can split up your sheet into smaller pieces, this may be better. For example, instead of having grandparent rows as the Project, what about 7 Project sheets with their own form? You could then use a Report to bring together certain rows across these sheets, as needed.

Unknown

This content has been removed.