Subtotal

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?
 
G

GS

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.
 
D

Don Guillett

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
 
G

GS

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

SpencerDavis

Assuming that the fors 0000075 is in A1 and $22.50 is in B1, enter
=IF(A1=A2,"",SUMPRODUCT(--($A$1:$A$1000=A1),$B$1:$B$1000)) into C1 and copy
down.

It will sum the totals of each number in A at the last. So row 4 in this
instance will sum the 4 values agains 0000075 giving you a total of $58.13.

You can change the 1000's in the two ranges in the formula to cover all the
data if more than 1000 rows.
You could also change them to just say A:A and B:B (if using Excel 2007 or
newer) but this will make the calculation slower.

Please note, this approach will not work if the numbers in A are not in
blocks. So if you had 0000075 in rows 1 & 3 and a different one in row 2,
this approach would probably not do what you're after.
 
Top