# Loop thru range within groupings not to exceed a sum of 90



## FryGirl (Jan 2, 2023)

Hello and Happy New Year too all,

I have a range of cells that in column R are the count of number of words in column Q by row.  I did not post the contents of column Q because they are not so much in the question.  In column S, I now have the cumulative total starting from R2.  Once the cumulative total in column S is less than or equal to 90, that would be the first group in the loop.  The next loop would start in R7 and count until the cumulative total is less than or equal to 90.  And so on.

With the set of data, I would expect 11 grouping, or the loop to process 11 times.  How can I achieve this loop, of is there a formula I can use in column S to get me the highest number in each grouping less than 90.  Basically, what I'm doing, column Q has some text that I'm transferring over to ppt.  I have all of that working, but right now it's static to just doing every three rows.  I would like to make it dynamic to select any number of rows but not greater than 90.  I hope this make sense.

R2:R6 = 90
R7:R9 = 67
R10:R12 = 79
R13:R15 = 76
R16:R19 = 90
and so on...

111129401252166822902411426140171572518223205312363026611277353122333527362163782440211413274403547523498115093053934573516242264645691367273976625791


----------



## DanteAmor (Jan 2, 2023)

Hello and happy new year to you too.

Check if this result is what you need:

Dante AmorAQRS1CountGroup211 329 412 516 62290724 826 917671025 1123 1231791330 1411 1535761623 1727 1816 1924902011 2127 2235732323 2411 2530642634 2751852822 2945673036 313975322525Hoja3Cell FormulasRangeFormulaS2:S32S2=IF(R3="",R2,IF(SUM($R$2:R3)-SUM($S$1:S1)<=90,"",SUM($R$2:R2)-SUM($S$1:S1)))


----------



## FryGirl (Jan 2, 2023)

Hi Dante and thanks a lot for the help,

Now I need to get a number I can use to build the resize component.  As you can see in column T, I started to work on a formula, but the results should be as in column U.

CountGroupResize1129121622905524261767832523317911330113576143232716249018411273573213231130642433451852622245672823639753022525311


----------



## FryGirl (Jan 2, 2023)

Hi Dante,  Here is what I've come up with.

U2 and Down -- =IF(N(S2),COUNT($R$2:R2)-MAX($T$1:T1),"")

Not sure if there is a way to combine column T and U into one

T2 and Down -- =IF(N(S2),COUNT($R$2:R2)-T1,"")

CountGroupResize1129121622905524261767832523317911330113576143232716249018411273573213231130642433451852622245672823639753022525311


----------



## DanteAmor (Jan 2, 2023)

Try this:

Dante AmorAQRST1CountGroupResize211  329  412  516  622905724  826  9176731025  1123  12317931330  1411  15357631623  1727  1816  19249042011  2127  22357332323  2411  25306432634  27518522822  29456723036  31397523225251Hoja3Cell FormulasRangeFormulaS2:S32S2=IF(R3="",R2,IF(SUM($R$2:R3)-SUM($S$1:S1)<=90,"",SUM($R$2:R2)-SUM($S$1:S1)))T2:T32T2=IF(R3="",COUNT($R$2:R2)-SUM($T$1:T1),IF(SUM($R$2:R3)-SUM($S$1:S1)<=90,"",COUNT($R$2:R2)-SUM($T$1:T1)))


----------



## DanteAmor (Jan 2, 2023)

Or this:

Dante AmorAQRST1CountGroupResize211  329  412  516  622905724  826  9176731025  1123  12317931330  1411  15357631623  1727  1816  19249042011  2127  22357332323  2411  25306432634  27518522822  29456723036  31397523225251Hoja3Cell FormulasRangeFormulaS2:S32S2=IF(R3="",R2,IF(SUM($R$2:R3)-SUM($S$1:S1)<=90,"",SUM($R$2:R2)-SUM($S$1:S1)))T2:T32T2=IF(S2="","",COUNT($R$2:R2)-SUM($T$1:T1))


----------



## FryGirl (Jan 2, 2023)

That is absolutely perfect.  Thank you!


----------



## Peter_SSs (Jan 2, 2023)

FryGirl said:


> That is absolutely perfect. Thank you!


Just checking ..
I have altered one of the column R values (yellow cell). Doesn't that make the orange cell incorrect? Perhaps I have not understood properly but I was thinking that the final row value should then be 81 per the modified formula in column T?

23 01 03.xlsmRST1CountGroupGroup211  329  412  516  6229090724  826  91767671025  1123  123179791330  1411  153576761623  1727  1816  192490902011  2127  223573732323  2411  253064642634  275185852822  294567673036  3120  32252581GroupsCell FormulasRangeFormulaS2:S32S2=IF(R3="",R2,IF(SUM($R$2:R3)-SUM($S$1:S1)<=90,"",SUM($R$2:R2)-SUM($S$1:S1)))T2:T32T2=IF(OR(SUM($R$2:R3)-SUM($T$1:T1)>90,R3=""),SUM($R$2:R2)-SUM($T$1:T1),"")


----------



## FryGirl (Jan 3, 2023)

Hi Peter, yes, after some testing, that is correct.  Your new updated formula caught it.  Thanks.


----------



## Peter_SSs (Jan 3, 2023)

You're welcome. Thanks for the confirmation.


----------

