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 ??).

“Doubt is uncomfortable, certainty is ridiculous.” (Voltaire)


0




Reply

Utf

4/7/2010 6:55:01 AM 

Subtotal feature or pivottable based of dynamic named range;
http://www.ozgrid.com/Excel/subtotal.htm
http://www.ozgrid.com/Excel/excelpivottables.htm
http://www.ozgrid.com/Excel/DynamicRanges.htm

Regards
Dave Hawley
www.ozgrid.com
"bud i" <ibud.nospam@bellsouth.net> wrote in message
news:8E02A42E60D64A1BBD93CDDCB163ED40@microsoft.com...
> 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 ??).
> 
> “Doubt is uncomfortable, certainty is ridiculous.” (Voltaire)


0




Reply

ozgrid

4/7/2010 7:42:52 AM


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

Rick (MVP  Excel)
"bud i" <ibud.nospam@bellsouth.net> wrote in message
news:8E02A42E60D64A1BBD93CDDCB163ED40@microsoft.com...
> 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 ??).
> 
> “Doubt is uncomfortable, certainty is ridiculous.” (Voltaire)


0




Reply

Rick

4/7/2010 8:07:01 AM


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)

Regards,
OssieMac
"bud i" wrote:
> 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 ??).
> 
> “Doubt is uncomfortable, certainty is ridiculous.” (Voltaire)


0




Reply

Utf

4/7/2010 8:36:01 AM



3 Replies
792 Views
(page loaded in 0.029 seconds)
