Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subtotals for a variable number of rows
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subtotals for a variable number of rows
Subtotal feature or pivottable based of dynamic named range;
http://www.ozgrid.com/Excel/subtotal.htm http://www.ozgrid.com/Excel/excel-pivot-tables.htm http://www.ozgrid.com/Excel/DynamicRanges.htm -- Regards Dave Hawley www.ozgrid.com "bud i" wrote in message ... 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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subtotals for a variable number of rows
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" wrote in message ... 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) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subtotals for a variable number of rows
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
variable number of rows. | Excel Programming | |||
Column Subtotals with variable number of items | Excel Programming | |||
Linking to a Variable Number of Rows - XP/07 | Excel Worksheet Functions | |||
Same operations on a variable number of rows | Excel Programming | |||
deleting variable number of rows | Excel Programming |