Replacing OFFSET to sum up to max value from specific cell

Hello everyone, I am new to the board & thank you all in advance for the help.

I have data for the time it takes each step in a manufacturing process & each step has a unique name that never repeats, see attached sample.

What I want to accomplish is have (2) inputs that I can change independently, which are, which step am I starting on (Start Point) & MAX time I have available, then return the total time up to that cell (Sum of Steps) without exceeding the max time & how many steps were completed (Count).

For example, as seen on table above, lets say I will be starting at "2800N3" (located in A4) & I only have 20 minutes (my given Max Time), & I want to know how many steps I will be able to complete in those 20 minutes and what is the actual total time (sum) up to that last completed step. In this case, with 20 minutes, I will complete steps 2800N3 & 2800N4, and the sum of those completed steps is 14, and the number of steps completed was 2.

Another example, Start Point: 2800N2, Max Time: 30, RESULTS: Sum of Steps: 22 & Count: 3, ie with 30 minutes we can complete 2800N2 thru 2800N4, which is 3 steps.

I was able to get this to work using a combination of match, index, subtotal and offset, however offset is a volatile function, and since I will have about 30 of these in the same cell (one for each mfg machine) it becomes really slow when changing values, so I would like to use something that in non-volatile in hopes the calculations are faster.

Welcome to the forum.

I created a helper column with this formula, say in H2, copied down:
=IF(COUNTIF(A$2:A2,$C$2),B2+H1,0)

Then this non-volatile formula to total time:

Formula:

Please Login or Register  to view this content.

And this to count steps:

Formula:

Please Login or Register  to view this content.

Please run some test in attached file and let us know how it goes.

Good luck!

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. As you are new here, I will do it fir you this time: https://www.mrexcel.com/board/thread...-cell.1197398/)

, and since I will have about 30 of these in the same cell (

With that thought in mind I will surmise that number could be variable, and with that thought in mind I propose using a dynamic named range (DNR) as opposed to cell address ranges. If you are not familiar with DNRs they resize to fit your data automatically. One is defined and stored in Name Manager.

It is also a way to do this without helper columns. Please find this listed in the attached and in Name Manager.

Time_Rnge

=INDEX(Sheet1!$B:$B,MATCH(Sheet1!$C$2,Sheet1!$A:$A,0)):INDEX(Sheet1!$B:$B,MATCH(25^25,Sheet1!$B:$B))

Then this formula in D3

Formula:

Please Login or Register  to view this content.

and this one in E3

Formula:

Please Login or Register  to view this content.

Not sure that this will speed up the process, however the formulas do not employ the OFFSET function nor are they array entered.
Column F on the Data sheet is populated using: =SUMIFS(D$2:D2,B$2:B2,B2)
On the Leads sheet in columns B, D, F and H:
1. Row 6 is populated using: =MATCH(B5,Data!$E2:$E60,0)
2. Row 7, which displays the number of rows to be summed, is populated using:

Formula:

Please Login or Register  to view this content.

3. Row 8 is populated using: =INDEX(Data!$C2:$C60,SUM(B6:B7)-1)
4. Row 9 is populated using: =SUM(INDEX(Data!$D2:$D60,B6):INDEX(Data!$D2:$D60,SUM(B6:B7)-1))
Let us know if you have any questions.