Sum by blocks

M

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,362
Office Version
  1. 2010
Hello,</SPAN></SPAN>

I need to sum column "C" depending on as per column "D" blocks (blocks consist with 0's or largest to lowest number ended with 1)</SPAN></SPAN>

Here is a small example... </SPAN></SPAN>


Book1
ABCDEF
1
2
3TotalTotal
42121
5
6101
7111
8002
900
1010
1100
1200
1310
1400
150133
16112
17111
18010
1909
2008
2107
2216
2305
2404
2503
2602
2701
28108
2900
3010
3100
3210
3300
3400
3500
3600
3710
3810
3900
4020
4100
4200
4300
4400
4510
461304
47029
48028
49027
50026
51025
52024
53023
54122
55021
56020
57119
58018
59017
60016
61115
62014
63013
64012
65011
66010
6709
6808
6907
7006
7105
7204
7303
7402
7501
76002
7700
7800
7900
8000
8110
8200
8300
8400
8500
8600
8700
8810
8900
9000
9100
9200
9300
Sheet1
Cell Formulas
RangeFormula
C4=SUM(C6:C93)
F4=SUM(F6:F93)
F6=SUM(C6)
F7=SUM(C7)
F8=SUM(C8:C14)
F15=SUM(C15:C27)
F28=SUM(C28:C45)
F46=SUM(C46:C75)
F76=SUM(C76:C93)


Thanks In Advance </SPAN></SPAN>
Using version 2000</SPAN></SPAN>

Regards,</SPAN>
Moti</SPAN></SPAN>
 
Last edited:

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
Hello, here is a clarifications referring to my post#1</SPAN></SPAN>

I were reading my request and observed did not mention what I need. </SPAN></SPAN>

I got nearly 20000 rows in the column "D" there are blocks with "0's" and whatever greater number starts always end with "1" as there are plenty of blocks it is difficult to sum one by one. </SPAN></SPAN>

What I need a VBA or a Formula in the column "F" which can track the blocks of column "D" and get SUM of each blocks separately as per shown example.</SPAN></SPAN>

Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>
 
Last edited:
Upvote 0
Try this:

Code: 
[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1092364a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1092364-sum-blocks.html'' section group looping[/COLOR][/I]
[COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], j [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] va, vb

va = Range([COLOR=brown]"D1"[/COLOR], Cells(Rows.count, [COLOR=brown]"D"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp))
Range([COLOR=brown]"F6:F"[/COLOR] & UBound(va, [COLOR=crimson]1[/COLOR])).Value = [COLOR=brown]""[/COLOR]
vb = Range([COLOR=brown]"F1:F"[/COLOR] & UBound(va, [COLOR=crimson]1[/COLOR]))

[COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]6[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR])
    [COLOR=Royalblue]If[/COLOR] va(i, [COLOR=crimson]1[/COLOR]) = [COLOR=crimson]0[/COLOR] [COLOR=Royalblue]Then[/COLOR]
        j = i
        [COLOR=Royalblue]Do[/COLOR]
            i = i + [COLOR=crimson]1[/COLOR]
            [COLOR=Royalblue]If[/COLOR] i > UBound(va, [COLOR=crimson]1[/COLOR]) [COLOR=Royalblue]Then[/COLOR] [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]Do[/COLOR]
        [COLOR=Royalblue]Loop[/COLOR] [COLOR=Royalblue]While[/COLOR] va(i, [COLOR=crimson]1[/COLOR]) = va(i - [COLOR=crimson]1[/COLOR], [COLOR=crimson]1[/COLOR])
        
        i = i - [COLOR=crimson]1[/COLOR]
        vb(j, [COLOR=crimson]1[/COLOR]) = WorksheetFunction.Sum(Range([COLOR=brown]"C"[/COLOR] & j).Resize(i - j + [COLOR=crimson]1[/COLOR]))
    [COLOR=Royalblue]Else[/COLOR]
        vb(i, [COLOR=crimson]1[/COLOR]) = WorksheetFunction.Sum(Range([COLOR=brown]"C"[/COLOR] & i).Resize(va(i, [COLOR=crimson]1[/COLOR])))
        i = i + va(i, [COLOR=crimson]1[/COLOR]) - [COLOR=crimson]1[/COLOR]
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
[COLOR=Royalblue]Next[/COLOR]

Range([COLOR=brown]"F1"[/COLOR]).Resize(UBound(vb, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]1[/COLOR]) = vb

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]
 
Upvote 0
Akuini said:
Try this:

Code: 
[FONT=lucida console][COLOR=royalblue]Sub[/COLOR] a1092364a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1092364-sum-blocks.html'' section group looping[/COLOR][/I]
[COLOR=royalblue]Dim[/COLOR] i [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]Long[/COLOR], j [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]Long[/COLOR]
[COLOR=royalblue]Dim[/COLOR] va, vb

va = Range([COLOR=brown]"D1"[/COLOR], Cells(Rows.count, [COLOR=brown]"D"[/COLOR]).[COLOR=royalblue]End[/COLOR](xlUp))
Range([COLOR=brown]"F6:F"[/COLOR] & UBound(va, [COLOR=crimson]1[/COLOR])).Value = [COLOR=brown]""[/COLOR]
vb = Range([COLOR=brown]"F1:F"[/COLOR] & UBound(va, [COLOR=crimson]1[/COLOR]))

[COLOR=royalblue]For[/COLOR] i = [COLOR=crimson]6[/COLOR] [COLOR=royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR])
    [COLOR=royalblue]If[/COLOR] va(i, [COLOR=crimson]1[/COLOR]) = [COLOR=crimson]0[/COLOR] [COLOR=royalblue]Then[/COLOR]
        j = i
        [COLOR=royalblue]Do[/COLOR]
            i = i + [COLOR=crimson]1[/COLOR]
            [COLOR=royalblue]If[/COLOR] i > UBound(va, [COLOR=crimson]1[/COLOR]) [COLOR=royalblue]Then[/COLOR] [COLOR=royalblue]Exit[/COLOR] [COLOR=royalblue]Do[/COLOR]
        [COLOR=royalblue]Loop[/COLOR] [COLOR=royalblue]While[/COLOR] va(i, [COLOR=crimson]1[/COLOR]) = va(i - [COLOR=crimson]1[/COLOR], [COLOR=crimson]1[/COLOR])
        
        i = i - [COLOR=crimson]1[/COLOR]
        vb(j, [COLOR=crimson]1[/COLOR]) = WorksheetFunction.Sum(Range([COLOR=brown]"C"[/COLOR] & j).Resize(i - j + [COLOR=crimson]1[/COLOR]))
    [COLOR=royalblue]Else[/COLOR]
        vb(i, [COLOR=crimson]1[/COLOR]) = WorksheetFunction.Sum(Range([COLOR=brown]"C"[/COLOR] & i).Resize(va(i, [COLOR=crimson]1[/COLOR])))
        i = i + va(i, [COLOR=crimson]1[/COLOR]) - [COLOR=crimson]1[/COLOR]
    [COLOR=royalblue]End[/COLOR] [COLOR=royalblue]If[/COLOR]
[COLOR=royalblue]Next[/COLOR]

Range([COLOR=brown]"F1"[/COLOR]).Resize(UBound(vb, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]1[/COLOR]) = vb

[COLOR=royalblue]End[/COLOR] [COLOR=royalblue]Sub[/COLOR][/FONT]
Click to expand...
Wow Akuini, eye blinks solution it is solved! I even cannot calculate how much time needed to sum block in block for 20000 row or might it would not have been possible to sum each block manually and so perfect.</SPAN></SPAN>

I appreciate a lot your time to help me out.</SPAN></SPAN>

Have a nice day </SPAN></SPAN>

Kind regards,</SPAN></SPAN>
Moti :)</SPAN></SPAN>
 
Upvote 0
You're welcome, glad to help, & thanks for the feedback.:)
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Extract a range of values
Replies
8
Views
342
Fluff
Fluff
F
  • Solved
A simple project update VBA that is not match the projects
Replies
9
Views
223
NoSparks
N
I
  • Solved
IF formula with drop down list
Replies
7
Views
191
kevin9999
K
N
  • Question
Count - specific consecutive order
Replies
6
Views
511
Peter_SSs
Peter_SSs
G
  • Question
Copy Range of Cells based on Criteria, Paste it on rows below it, Do This Until All conditions are met, Then Delete All Copied Rows
Replies
0
Views
219
glaccounting
G

Forum statistics

Threads
1,214,896
Messages
6,122,132
Members
449,066
Latest member
Andyg666

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top