I'm trying to sum a range of cells to reach a certain target. Once that target is met, I need to know the number of rows it took to reach that target value and retrieve the value from an adjacent cell.
In this example I need to sum up rows of apples&bananas to get a total fruit count. Once that fruit count reaches a target (40) I need to know the #rows it took to get there and what the associated color is from the adjacent cell.
Ideally I would start at a particular bottom cell and work my way up until the sum reached the target. This seems to really complicate things but it's how my data source is structured.
If it's way too complicated to do it bottom-up, then I can work with a developer to get the dataset flip-flopped so that I can work with it top-down.
I've seen a few example formulas that are close but they always deal with summing up a single column and I've been unable to adjust it properly to make it a range-based sum. I'm also not 100% sure how to convert these into something that would produce the associated color.
excel.range.sum.until.target2.jpg
Here are the examples I've found while searching that get me part-of-the-way to where I need to be (*Note these only work top-down):
=SUM(OFFSET(A2,0,0,MAX(INDEX((SUBTOTAL(9,OFFSET(A2,0,0,ROW(1:99999),1))<F1)*ROW(1:99999),,))+1,1))
=MAX(INDEX((SUBTOTAL(9,OFFSET(A2,0,0,ROW(1:99999),1))<F1)*ROW(1:99999),,))+1
Bookmarks