Solved: Sum Function

[winxmun]

Hi there,

Anybody can assist me based on the following tables?
How to auto sum up the Amt as per a/c type?
E.g auto sum up Amt for a/c no. start with 110 & appear at Total Amt.


A/c No Amt
110-001 33,369
110-002 1,577
110-003 0
111-001 20,000
111-003 25,000
112-002 30,000
112-005 35,000

A/c Type Total Amt
110 auto sum
111 auto sum
112 auto sum

[Charlize]

Columns A, B are used and C is the total amount / account.Sub Total_per_Account()
Dim rng As Range
Dim cell As Range
Dim vsum As Long
Set rng = Worksheets(1).Range("A2:A" & _
Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row)
vsum = 0
For Each cell In rng
vsum = vsum + cell.Offset(, 1).Value
If Left(cell.Offset(1, 0), 3) <> Split(cell, "-")(0) Or _
cell.Offset(1, 0).Value = vbNullString Then
cell.Offset(0, 2).NumberFormat = "0.00"
cell.Offset(0, 2).Value = vsum
vsum = 0
End If
Next cell
End Sub

[rory]

I would add a new column with a formula like =LEFT(A1,FIND("-",A1)-1) to get the digits before the dash in the A/C No., then use a pivot table and add this new column to the row fields and add the Amt to the data fields.

[Bob Phillips]

=SUMPRODUCT(--(LEFT(A2:A8,3)="110"),B2:B8)

etc.

You can replace the "110" with a reference to your summary cell.

[Charlize]

Since I've tried to find a formula solution (why do I keep trying if xld has such a simple solution), and put some time in it, I'll post it. But you need a helper column in column D to seperate to first three characters of column A.

=IF(AND(LEFT(A1;3)=LEFT(A2;3);LEFT(A2;3)=LEFT(A3;3));""
;IF(LEFT(A2;3)=LEFT(A3;3);"";SUMIF(D$2:D$1000;LEFT(A2;3);B$2:B$1000)))It's possible that you need to replace the ; with , . The formula is for column C.
For the helper column you could use
LEFT(A2;3) and copy that down.

[rory]

You could also use SUMIF:
=SUMIF($A$1:$A$6,A9&"*",$B$1:$B$6)
where A1:B6 contains the data and A9 contains the summary number (e.g. 110)

[winxmun]

Tks Rory. My problem has been solved! :ole: