Count of rows in range sum until target is met, preferably working from the bottom up

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

If I were doing this I'd just split it into a few different columns.
Column D would be the horizontal sum (e.g. =SUM(A2:B2) or however many columns across there are).
Then column E the running total. So starting from the bottom, say the last row is 14 then E14 = D14, E13 = D13 + E14, E12 = D12 + E13, etc.

Then in a cell somewhere else you can lookup the first row in Column E that is greater than the target. Could use something like =LARGE(IF(E$2:E$14>=40,ROW(E$2:E$14),""),1), which returns the largest row whose value is greater to or equal to 40.

Then to find the total rows that it spans you just take the difference of that value from the bottom row.

Not the perfect solution but hope it helps

edit: to find the associated colour just do index(A:A, {value that you got for the row}, 1)

Hi - thank you for the reply. This would work except the "last row" that needs to be the starting point for the sum is variable. So I can't create a fixed column with the upwards-heading SUM because I'll re-use this formula in a bunch of places and one time it might be row E14, another time it might be E8, etc.