Sum Columns A without filling data in

[anthonyexcel]

I have been struggling with this for a while. Is there a way to sum data without having school names all of the way down Column A. I get a lot of data like this and I have to go back and fill in the schools to used Sumif or Sumproduct. I would like to find a way to do it without filling in the schools. Any help would be appreciated!!!!

<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC ;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC ;
}
.tableizer-table th {
background-color: #104E8B ;
color: #FFF ;
font-weight: bold;
}
</style>

School	Totals					School	Total
Jefferson	1					Jefferson	19
	2					Washington	32
	3					Lincoln	6
	4					Johnson	100
	2
	3
	4
Washington	23
	3
	2
	4
Lincoln	2
	4
Johnson	4
	2
	43
	2
	4
	2
	4
	2
	1
	2
	34

<thead>
</thead><tbody>
</tbody>

 

[MickG]

Try this:-
Result start "G1".

[COLOR="Navy"]Sub[/COLOR] MG29Mar32
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("B2", Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Offset(, -1)
        Txt = IIf(Dn.Value = "", Txt, Dn.Value)
        .Item(Txt) = .Item(Txt) + Dn.Offset(, 1).Value
    [COLOR="Navy"]Next[/COLOR]
Range("G1:H1") = Array("School", "Totals")
Range("G2").Resize(.Count, 2) = Application.Transpose(Array(.Keys, .items))
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Fluff]

Alternatively you could use

Sub anthonyexcel()
   With Range("A2", Range("B" & Rows.Count).End(xlUp))
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
      .Value = .Value
   End With
End Sub

To fill in the school names & then use your normal formulae.

 

[anthonyexcel]

I love VBA!!!! Nice!!! Thank you both for your help and guidance!!! I will gladly use both versions!!!

Just out of curiosity, is there anyway to do this sort of thing with a formula?

 

[Fluff]

Very possibly, but that's way beyond my formula knowledge.