Subtotal

  • Thread starter Thread starter Cory Tilton
  • Start date Start date
C

Cory Tilton

here is my situation.

0000075 $22.50
0000075 $5.86
0000075 $21.74
0000075 $8.03
0000098 $26.83
0000098 $8.17
0000098 $23.25
0000098 $8.64
0000204 $12.43
0000204 $2.30
0000204 $10.42
0000204 $5.23
0000420 $33.24
0000420 $17.77
0000420 $39.22
0000420 $11.64
0000447 $14.43
0000447 $8.09
0000447 $12.73
0000692 $23.13
0000692 $8.43
0000711 $0.00


I want to subtotal the dollar amounts that have like numbers. For
instance, all of the 0000075's I want to sum all of the corresponding
dollar amounts to one value. Is there anyway to do this?
 
Cory Tilton was thinking very hard :
here is my situation.

0000075 $22.50
0000075 $5.86
0000075 $21.74
0000075 $8.03
0000098 $26.83
0000098 $8.17
0000098 $23.25
0000098 $8.64
0000204 $12.43
0000204 $2.30
0000204 $10.42
0000204 $5.23
0000420 $33.24
0000420 $17.77
0000420 $39.22
0000420 $11.64
0000447 $14.43
0000447 $8.09
0000447 $12.73
0000692 $23.13
0000692 $8.43
0000711 $0.00


I want to subtotal the dollar amounts that have like numbers. For
instance, all of the 0000075's I want to sum all of the corresponding
dollar amounts to one value. Is there anyway to do this?
Click to expand...

Assuming these values are in A:B, if they're grouped as shown then the
subtotals need a column to the right. (This would be easier than
putting rows between the groups)

Assuming the data has headers in row1, enter the following formula in
C5 to subtotal the 1st group of numbers:

=SUMIF($A:$A,$A5,$B:$B)

Note that this formula uses column-absolute, row-relative syntax so it
will auotmagically adjust for whatever row it gets copied to. In fact,
you could select all cells to receive the formula, type it once, then
use Ctrl+Enter to put it into all selected cells in one shot.

Otherwise, copy this to the last row of each group. Alternatively, you
could put it in the first row if you want the subtotals at the top of
each group.
 
If you don't have too many (makes it slow then use a macro to find the
blocks) You could use the built in data>subtotals or a macro I just
recorded to do it
Sub Macro4()
'
' Macro4 Macro
' Macro recorded 2/14/2012 by Donald B. Guillett
'

'
Range("A1:B22").Select
Selection.Subtotal GroupBy:=1, Function:=xlSum,
TotalList:=Array(2), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub
 
Don Guillett formulated on Tuesday :
If you don't have too many (makes it slow then use a macro to find the
blocks) You could use the built in data>subtotals or a macro I just
recorded to do it
Sub Macro4()
'
' Macro4 Macro
' Macro recorded 2/14/2012 by Donald B. Guillett
'

'
Range("A1:B22").Select
Selection.Subtotal GroupBy:=1, Function:=xlSum,
TotalList:=Array(2), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub
Click to expand...

Yeah, but when there's a lot of data blocks needing subtotals it takes
way too long to use that feature.

Actually, if you copy my formula down an entire column it would show
the same subtotal for every occurance of the value in colA, meaning you
wouldn't need to scroll a long list to see it (which is why I mentioned
putting it at the top of each group).
 
Back
Top