Group By SUM

hatahetahmad

New Member
Joined
Jan 8, 2016
Messages
31
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
    Anmerkung 2020-06-10 140728.jpg
    81 KB · Views: 6

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,909
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
10,836
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Jan 8, 2016
Messages
31

ADVERTISEMENT

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

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,836
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Aug 18, 2015
Messages
10,219
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,937
Messages
5,525,732
Members
409,661
Latest member
pprabha

This Week's Hot Topics

Top