Group By SUM

[hatahetahmad]

Hello, I have very simple data, that I want to group by SUM

As the uploaded image shows. My Data has 3 columns, first comes Index, Numbers then Group, which I want to fill according to specific limit cell H2.
I started by summing like cumulative column, then divided the result by the limit and rounded up to 0 decimals.
It showed me groups but some groups have more than the limit, therefor incorrect solution.

Results should be: Cell C2 and C3 should show 1 , Cell C4 should show 2 , Cell C5 and C6 should show 3 , Cell C7 and C8 should show 4 , Cell C9 should show 5 ...

The Idea is straight but not simple to execute.
I'm open to any solution using Excel Formulas, DAX or M

You can download the file from here

test group by sum.xlsx

drive.google.com

Please advise me.
Many Many Thanks

 

[pjmorris]

I think the key is understanding what you mean by ' summing like cumulative'? I don't understand which cells you are summing. The index column simply increments by 1 and the numbers column appears random so it appears to be impossible to group any of the numbers.

Pretty sure this isn't difficult, but need to better understand what you're doing.

 

[jasonb75]

I don't think you're going to be able to do it without a helper column.

Book2
	B	C	D
1	Number	Group Sum	Group
2	16	16	1
3	12	28	1
4	25	25	2
5	35	35	3
6	12	47	3
7	33	33	4
8	14	47	4
Sheet10

Cell Formulas
Range	Formula
C2:C8	C2	=IF(SUM(B2,C1)>50,B2,SUM(B2,C1))
D2:D8	D2	=IF(C2=B2,N(D1)+1,D1)

 

[hatahetahmad]

Thank you very much jasonb75

It worked as I wanted.

Many Thanks.

 

[hatahetahmad]

@pjmorris Thank you for the reply. I'll work on my explanation in my next questions

 

[jasonb75]

You're welcome, thanks for the feedback

I'll work on my explanation in my next questions

Please use the XL2BB add in to post your examples as well. Formulas can not be tested on screen captures, copy and paste often corrupts data with zero width spaces.
Many users can not or will not download attached files for a number of reasons. Sometimes people answering questions will ask you to upload a copy of your workbook, but normally only as a last resort for problems that are not easily diagnosed without.

 

[Eric W]

Here's a way without a helper column:

Book4
	A	B	C	D	E	F	G	H
1	Index	Number	Group					GroupBy
2	1	16	1					50
3	2	12	1
4	3	25	2
5	4	35	3
6	5	12	3
7	6	33	4
8	7	14	4
9	8	18	5
10	9	35	6
11	10	43	7
12	11	35	8
13	12	19	9
14	13	47	10
15	14	27	11
16	15	43	12
17	16	44	13
18	17	26	14
19	18	28	15
20	19	10	15
21	20	45	16
22	21	43	17
23	22	37	18
24	23	26	19
Sheet10

Cell Formulas
Range	Formula
C3:C24	C3	=IF(B3+SUMIF(C$2:C2,C2,B$2:B2)>$H$2,C2+1,C2)

 

[hatahetahmad]

Here's a way without a helper column:

Book4
	A	B	C	D	E	F	G	H
1	Index	Number	Group					GroupBy
2	1	16	1					50
3	2	12	1
4	3	25	2
5	4	35	3
6	5	12	3
7	6	33	4
8	7	14	4
9	8	18	5
10	9	35	6
11	10	43	7
12	11	35	8
13	12	19	9
14	13	47	10
15	14	27	11
16	15	43	12
17	16	44	13
18	17	26	14
19	18	28	15
20	19	10	15
21	20	45	16
22	21	43	17
23	22	37	18
24	23	26	19
Sheet10

Cell Formulas
Range	Formula
C3:C24	C3	=IF(B3+SUMIF(C$2:C2,C2,B$2:B2)>$H$2,C2+1,C2)

Thank you