Subtotals for a variable number of rows

  • Follow


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/excel-pivot-tables.htm
http://www.ozgrid.com/Excel/DynamicRanges.htm


-- 
Regards
Dave Hawley
www.ozgrid.com
"bud i" <ibud.no-spam@bellsouth.net> wrote in message 
news:8E02A42E-60D6-4A1B-BD93-CDDCB163ED40@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.no-spam@bellsouth.net> wrote in message 
news:8E02A42E-60D6-4A1B-BD93-CDDCB163ED40@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
785 Views

(page loaded in 0.085 seconds)


Reply: