Need to Sum Columns within a Column!

[FinancialAnalystKid]

I have about 30,000 rows of raw data.

There are rows of data per employee and then seperated by blank columns.

I need to total a column for each employee section.

I put in a TOTAL cell at the bottom of each employee section but summing each is tedious and will take forever!

I need a macro to go down column "K" find the word "TOTAL" and to the right of that cell (Column L) sum just that section.

So basically currently there is a cell labeled TOTAL on K15. on L15 I need it to sum(L2:L14)

The next TOTAL is on K27. on L27 I need it to sum(L17:L26)

...and so forth. Does this make sense?

Any help is MUCH appreciated.

 

[MrKowz]

Try:

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Values</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">3</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">9</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">10</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">2</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">11</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">17</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">TOTAL</td><td style="text-align: right;;">52</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">19</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">16</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">8</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">20</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">16</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">4</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">9</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">16</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">TOTAL</td><td style="text-align: right;;">108</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">16</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">18</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">14</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">1</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style=";">TOTAL</td><td style="text-align: right;;">49</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet2</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">L2</th><td style="text-align:left">=IF(<font color="Blue">K2="TOTAL",SUM(<font color="Red">INDEX(<font color="Green">$K$1:K1,MATCH(<font color="Purple">REPT(<font color="Teal">"z",255</font>),$K$1:K1</font>)</font>):K1</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />

 

[FinancialAnalystKid]

I'm trying to get away from manually entering a formula all throughout the sheet. I can go down manually and hit <alt> = but there are too many employees to do this for.

So I was looking for a macro to find TOTAL and to the right of it sum the data, etc...

Thanks for the effort on the function.

FAK

 

[MrKowz]

That's exactly what this formula does. Place it in cell L2 and copy it down.

If it doesn't work, provide some sample data and I will tailor it more.

 

[FinancialAnalystKid]

That's exactly what this formula does. Place it in cell L2 and copy it down.

If it doesn't work, provide some sample data and I will tailor it more.

There is data in column L2.

How do I post sample data? I don't have the HTML Maker and it doesn't seem available.

Can I post a screenshot somehow?

 

[FinancialAnalystKid]

Here's a screenshot.

 

[FinancialAnalystKid]

Simple terms: A macro that finds each cell with "TOTAL" then to the right of that cell SUM UP to the first blank cell (which is the set of data for that specific employee)

 

[MrKowz]

Try:

Public Sub FinancialAnalystKid()
Dim LR      As Long, _
    rng     As Range, _
    rngprev As Range, _
    rng1    As String
    
Application.ScreenUpdating = False
LR = Range("K" & Rows.Count).End(xlUp).Row
Set rngprev = Range("K1")
With Range("K2:K" & LR)
    Set rng = .Find("TOTAL", LookIn:=xlValues)
    If Not rng Is Nothing Then
        rng1 = rng.Address
        Do
            rng.Offset(0, 1).Formula = "=SUM(" & rngprev.Address & ":" & rng.Address & ")"
            Set rngprev = rng
            Set rng = .FindNext(rng)
        Loop While Not rng Is Nothing And rng.Address <> rng1
    End If
End With
Application.ScreenUpdating = True
End Sub

 

[FinancialAnalystKid]

Wow...

Almost!

What the macro did was sum Column "K" data and went too high.

Example on the image I posted: It summed K15:K27 instead of L17:L26!

I looked at the code and can't figure out how to tweak it.

Thanks in advance if you can fix it!

FAK

 

[MrKowz]

Try:

Public Sub FinancialAnalystKid()
Dim LR      As Long, _
    rng     As Range, _
    rngprev As Range, _
    rng1    As String
    
Application.ScreenUpdating = False
LR = Range("K" & Rows.Count).End(xlUp).Row
Set rngprev = Range("K1")
With Range("K2:K" & LR)
    Set rng = .Find("TOTAL", LookIn:=xlValues)
    If Not rng Is Nothing Then
        rng1 = rng.Address
        Do
            rng.Offset(0, 1).Formula = "=SUM(" & rngprev.Offset(1, 1).Address & ":" & rng.offset(-1, 1).Address & ")"
            Set rngprev = rng
            Set rng = .FindNext(rng)
        Loop While Not rng Is Nothing And rng.Address <> rng1
    End If
End With
Application.ScreenUpdating = True
End Sub

This code might capture an extra cell (that should always be blank, given the layout of your data), so it will not affect the overall calculations.