# Group By SUM

#### hatahetahmad

##### New Member
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

Please advise me.
Many Many Thanks

#### Attachments

• Anmerkung 2020-06-10 140728.jpg
81 KB · Views: 6

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

#### pjmorris

##### Well-known Member
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

##### Well-known Member
I don't think you're going to be able to do it without a helper column.
Book2
BCD
1NumberGroup SumGroup
216161
312281
425252
535353
612473
733334
814474
Sheet10
Cell Formulas
RangeFormula
C2:C8C2=IF(SUM(B2,C1)>50,B2,SUM(B2,C1))
D2:D8D2=IF(C2=B2,N(D1)+1,D1)

#### hatahetahmad

##### New Member
Thank you very much jasonb75

It worked as I wanted.

Many Thanks.

#### hatahetahmad

##### New Member

ADVERTISEMENT

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

#### jasonb75

##### Well-known Member
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

##### MrExcel MVP
Here's a way without a helper column:

Book4
ABCDEFGH
1IndexNumberGroupGroupBy
2116150
32121
43252
54353
65123
76334
87144
98185
109356
1110437
1211358
1312199
14134710
15142711
16154312
17164413
18172614
19182815
20191015
21204516
22214317
23223718
24232619
Sheet10
Cell Formulas
RangeFormula
C3:C24C3=IF(B3+SUMIF(C\$2:C2,C2,B\$2:B2)>\$H\$2,C2+1,C2)

#### hatahetahmad

##### New Member
Here's a way without a helper column:

Book4
ABCDEFGH
1IndexNumberGroupGroupBy
2116150
32121
43252
54353
65123
76334
87144
98185
109356
1110437
1211358
1312199
14134710
15142711
16154312
17164413
18172614
19182815
20191015
21204516
22214317
23223718
24232619
Sheet10
Cell Formulas
RangeFormula
C3:C24C3=IF(B3+SUMIF(C\$2:C2,C2,B\$2:B2)>\$H\$2,C2+1,C2)

Thank you

Replies
2
Views
88
Replies
3
Views
114
Replies
1
Views
209
Replies
0
Views
72
Replies
2
Views
97

Threads
1,119,121
Messages
5,576,213
Members
412,708
Latest member
AD04