How to list unique values under a parent row, and summarize in the same cell

Kelsey Ciarrocca

Is it possible to list the unique values listed under a parent cell, and give a sum of the values, all in the same cell? When I set my sheet up, I though the "Funded Work Items" would stay the same each month, so right now my sum in the blue, calls the funded work items in the month of October.

Here's a sample of it:

"=[Vehicle/Boat/ATV Number]4234 + ": $" + SUMIF([Funded Work Item]4233:[Funded Work Item]4389, [Vehicle/Boat/ATV Number]4232, Cost4233:Cost4389) + " YTD" +"

What I'd like, is to be able to have it list the unique values for the whole year, and then list the sum of that "cost". Is this possible? I'm willing to get creative with it. I just don't want it to be so hard coded as it is right now. Thank you!

Paul Newcome

Ok. So lets give this a whirl...

Insert a text/number column called [Funded Helper]. Then we can insert this formula into all rows:

=IF(Level@row = MAX(DESCENDANTS(Level@row)) - 1, [Funded Work Item]@row + ": $" + Cost@row + "" + IF(LEN(Cost@row - INT(Cost@row)) = 1, ".00", IF(LEN(Cost@row - INT(Cost@row)) = 3, "0")) + " YTD")

Next we go to the [Funded Work Item] column.

In your top most level (Engineering):

=JOIN(COLLECT(DESCENDANTS([Funded Helper]@row), DESCENDANTS(Level@row), MAX(DESCENDANTS(Level@row)) - 1), CHAR(10))

And in the next level down (October 2020):

=SUBSTITUTE(JOIN(COLLECT(DESCENDANTS([Funded Helper]@row), DESCENDANTS(Level@row), MAX(DESCENDANTS(Level@row)) - 1), CHAR(10)), "YTD", "")

Try running that through the paces and see if we need to adjust anything.

Mark Cronk

Hi @Kelsey Ciarrocca ,

I don't think there's an easier way to do this using the format and constraints you have in place. As an alternative, you should consider keeping all of your transactions in a single sheet without the complex hierarchy and use a report to generate the summary view. The new report builder allows you to group and summarize.

I'm curious to see if someone has a really creative solution for you.

Mark

Genevieve P.

Hi @Kelsey Ciarrocca

I agree with Mark - the issue is that your Parent rows have text in with the values so it becomes tricky to extract what it is you want to SUM. You may be able to create a Report from this current sheet and exclude the parent rows, then Group it by those 4 Values to have a quick overview of the year.

At the very least, your COST column could be easily calculated because the next level of Child rows seems to have plainly numerical data. That top cell in the COST column could be:

=SUM(CHILDREN()) + " YTD"

Is that what you have already?

Paul Newcome

I feel like we might be able to automate it in the sheet with a few helper columns. I am going to do some testing and see if I can figure something out...

Kelsey Ciarrocca

@Genevieve P Hi Genenvieve- The direction you're going there, using the "PARENT" & "CHILDREN functions was how I was originally hoping to build this sheet. My lines in white that have text with the sum, those use the "children" function. But once I tried using that in the "Cost" in the blue or grey lines it returns a 0, or an error. I also seemed to max out how many times you can have a function look at an entire column. I started getting "circular reference" errors. So that's another hard coded bummer I have about my formulas. Any idea what that is? It's like the search criteria within a column can't over lap.

Kelsey Ciarrocca

@Mark Cronk I appreciate you giving it a look! I made this sheet for an overwhelmed new admin, and the hope was to make this data entry easy and help her report sums without having to do anything. Despite how much time I've put into this sheet, I might need to consider making a more simple sheet, and create a report like you said.

Would I be able to build a single report that breaks it down similar to the grey line? I think the reason I originally did this, was because she wanted to be able to see things broken down by Field Site, and then what labor codes (funded work items) are being charged, how much they are being charged a month, and WHAT vehicle (tracking by license plate). It felt like a lot, and I couldn't wrap my head around how I could do this in a report.

Kelsey Ciarrocca

@Paul Newcome Looking forward to seeing what you come up with! I really appreciate it :) I've used a few other solutions you've come up with other people (like the helper for creating new lines)- so I appreciate you putting your mind to this!

Paul Newcome

Figured out for the columns where you just have numbers, but I am still working on the [Funded Work Item] column.

Insert a helper column I called "Level". In this column enter the following into every single row:

=COUNT(ANCESTORS())

In the parent rows you can use...

=SUMIFS(DESCENDANTS(), DESCENDANTS(Level@row), MAX(DESCENDANTS(Level@row))) + " Specific Text"

I will revisit once I have played around with the [Funded Work Item] column, but in the meantime feel free to let me know if the above works for the other columns.

Paul Newcome

@Kelsey Ciarrocca Will there be additional hierarchy levels, or will it always be those 4?

Kelsey Ciarrocca

@Paul Newcome There is one more at the very top, that summarizes everything - without any constraints, just general totals for everything. I'm okay getting rid of that one though if I can get something to work for these 4!

Kelsey Ciarrocca

@Paul Newcome Here is the full formula, in all it's ugly and overwhelmingness, haha. I got the "helper" part from you in another post :) The dream is for it to have the flexibility to adapt to when new unique "Funded Work Items" are added to a month. It's so hard coded right now, that if someone has a funded work item now listed, it won't be counted.

Paul Newcome

Ok. So lets give this a whirl...

Insert a text/number column called [Funded Helper]. Then we can insert this formula into all rows:

=IF(Level@row = MAX(DESCENDANTS(Level@row)) - 1, [Funded Work Item]@row + ": $" + Cost@row + "" + IF(LEN(Cost@row - INT(Cost@row)) = 1, ".00", IF(LEN(Cost@row - INT(Cost@row)) = 3, "0")) + " YTD")

Next we go to the [Funded Work Item] column.

In your top most level (Engineering):

=JOIN(COLLECT(DESCENDANTS([Funded Helper]@row), DESCENDANTS(Level@row), MAX(DESCENDANTS(Level@row)) - 1), CHAR(10))

And in the next level down (October 2020):

=SUBSTITUTE(JOIN(COLLECT(DESCENDANTS([Funded Helper]@row), DESCENDANTS(Level@row), MAX(DESCENDANTS(Level@row)) - 1), CHAR(10)), "YTD", "")

Try running that through the paces and see if we need to adjust anything.

Paul Newcome

And if you wanted to adjust the formula in your Cost column so that it shows the $ and always has 2 decimals before the "YTD" let me know. That is actually already built in to the above for the formula in the [Funded Helper] column, so it is a matter of simply pulling the appropriate portion and dropping it in.

Paul Newcome

And another afterthought (hopefully the last thing I forget to mention haha), you are going to want to make sure that "Wrap Test" is enabled in the [Funded Work Item] column to ensure the line breaks are visible.

Kelsey Ciarrocca

@Paul Newcome Hey Paul! I'm on the first step (super excited to try this), but it says unparsable. I'm try to look for the typo now. If you're able to bet me to it, please let me know!

Am I supposed to have something(like a column) called "Level" in mine? - wait sorry just saw a previous step I need to do!

Also, the "levels" column works for the most part, but it does not work for the blue line. The ancestor count for that is 0... not sure if that is why or not.

Kelsey Ciarrocca

@Paul NewcomeHey Paul, I'm not able to get the "Funded Helper" row to populate. For the parent rows, it's blank, and then for the level 2 & 3 of Levels (the white lines), it's saying invalid operation. Any ideas?