Subtotals for a variable number of rows

  • Thread starter Thread starter bud i
  • Start date Start date
B

bud i

Following is my data:
A B C
1 Oranges 6
2 Oranges 5
3 Oranges 7
4 Pomegranates 19
5 Pomegranates 16

C3 should be 18, and C5 should be 35.
Next week there will more more or fewer categories with a variable number of
entities in each.

A macro to accomplish this will save me a lot of time (and errors ??).
 
Give this macro a try...

Sub SubTotals()
Dim X As Long, LastRow As Long, LastSubTotal As Long, Fruit As String
Const StartRow As Long = 1
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Fruit = Cells(StartRow, "A").Value
LastSubTotal = StartRow
For X = StartRow + 1 To LastRow + 1
If Cells(X, "A").Value <> Fruit Then
Cells(X - 1, "C").Value = WorksheetFunction.Sum(Range(Cells( _
LastSubTotal, "B"), Cells(X - 1, "B")))
Fruit = Cells(X, "A").Value
LastSubTotal = X
End If
Next
End Sub
 
Hi Bud,

Is it really necessary to have the formula in column C adjacent to the last
record of the particular type? If not, you could set up a little table either
on the same worksheet on on another worksheet like the following with a list
of unique values of your column A.

Col E Col F
Item Total
Oranges 18
Pomegranates 35

You can then use SUMIF. See Help for more info on this.

Your formula in F2 in the above would be
=SUMIF(A:A,E2,B:B)

You only need to copy the formula down and you can add or delete items as
required.

If you decide to have your table in another worksheet then the formula would
be like the following. (Assuming the table is in columns E and F)

=SUMIF(Sheet1!A:A,E2,Sheet1!B:B)
 
Back
Top