Sum by blocks

M

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,422
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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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

T
  • Question Question
RAINFALL CESSATION DATE USING INDEX
Replies
1
Views
245
Thlama
T
B
  • Solved
Extract a range of values
Replies
8
Views
526
Fluff
Fluff
J
  • Solved
conditional formatting based on duplicate cells
Replies
4
Views
258
Joe4
Joe4
F
  • Solved
A simple project update VBA that is not match the projects
Replies
9
Views
417
NoSparks
N
I
  • Solved
IF formula with drop down list
Replies
7
Views
311
kevin9999
K

Forum statistics

Threads
1,224,820
Messages
6,181,161
Members
453,021
Latest member
Justyna P

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