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

Kelsey Ciarrocca

@Paul Newcome Okay, I got it up and running for the most part! I am blown away with your work around! lol Really impressive job.

I was not able to get Funded Helper to work fully. After much fiddling with it, I ended up having to take out the parts that handle the ".00" and "0".

The top most layer of Funded Work Items (Engineering, in our example), did not work. It returned "1", which seemed to be the ancestor number plus 1. So I still need to work on that.

The second layer though, for each month, worked! I am blown away. Thank you so much!!!

Paul Newcome

@Kelsey Ciarrocca For my testing, I tried to replicate your initial screenshot including the column names. HERE is the published sheet. In the two leftmost columns are the formulas that seem to be causing some issues for you. Please take a look at the published sheet and let me know if you see any errors on my part.

Paul Newcome

Oh. And I forgot to mention (go figure) that the solution for the Funded Helper column assumes that you already have the parent rows of the Cost column working.

Kelsey Ciarrocca

@Paul Newcome Okay, I was able to get the formulas to work! THANK YOU! This was seriously some kick a$$ help lol.

The one for the TYD Summaries ended up listing everything for the year, duplicating many funded work items and the list was about 50 items long. I think I'll have to approach that one differently, but I really love the approach you were going for. At that point do you think my best bet would be to just have a TYD report?

Paul Newcome

Happy to help! 👍️

Can you post a screenshot of the YTD that had the duplicates listed? I'll see if I can work on that for you as well if you'd like. It may require more helper columns, but we should be able to either work in the DISTINCT function or some other workaround. I do have a few ideas, but it would be helpful if I could visualize exactly what you are talking about.

Kelsey Ciarrocca

@Paul Newcome Here's a screen shot of what it looks like :)

I undid the formula in the next screen shot, so that you could see what the summaries below it look like... in case that helps.

Paul Newcome

Ok. That's what I thought was going on. The Level 1 summaries look good. It is just the level 0 summary that needs some work. I think I may be on to something, but I will let you know for sure once I have it all worked out. I am working in the same published sheet, so you will be able to use that same link to check out the results once I am done.

Kelsey Ciarrocca

@Paul Newcome You're awesome, Paul! Thank you so much.

Paul Newcome

Ok. Take a look now.

I adjusted the formula in the Level 1 summary rows as well as the [Funded Helper] column as the adjustments made certain elements completely unnecessary.

I then added 4 additional columns and adjusted the Level 0 summary formula.

Kelsey Ciarrocca

@Paul Newcome Alright, I wasn't able to get it to work on my end, but I'm sure that is user error. Looks like you were able to get it to work, so I'll just keep playing around with it. Really impressive use of different functions. I literally never would've thought of that. You're pretty much a genius in my book.

Paul Newcome

If you would like, you can post a link to the sheet. I'll then request access and can take a look directly if you are able to go that route.

Kelsey Ciarrocca

@Paul Newcome I just created a slimmed down version:

Here

Kelsey Ciarrocca

@Paul Newcome So this is good and I think it's almost there, but it's still calling on all of the Funded Work Items. Is there a way to add an if statement to this, and I can specify the Field Site?

=JOIN(COLLECT(YTD:YTD, YTD:YTD, @cell <> ""), CHAR(10))

Paul Newcome

@Kelsey Ciarrocca I took a look at the sheet, but apparently there is a little too much going on for my computer to handle and it kept crashing. I was able to get a general idea of what was there though, and it looks like it is working as intended thus far.

What exactly are you wanting to do with the IF? I'm not sure I follow what you mean by specifying the field site.