Sum Data Contains String/Mark from Multiple Sheet into Same Cell

[muhammad susanto]

hi all..
i have dozen sheets, i want to sum data from multiple sheets. My position data always in cell C20 for each sheet.
my data contains string, mark =, space (in front mark=), mark . or ,
how to do it this:
here my data

LPK-2.010 All.xlsx
	G
2	data
3	= 877.000,00
4	= 1.200.000,00
5	= 10.877.000,00
6	= 115.877.000,00
7	= 1.877.500.000,00
8	= 10.900.600.000,00
9	= 123.300.400.000,00
10	sum??
Sheet1

any help, thanks all..
note :
can use formula or macro
.sst

 

[JoeMo]

hi...
yes. I have use regional setting use "." as the decimal separator and "," as the thousands separator. Of course yes, always use two digits after the last.

OK. Here's some code that carries out the sum to two decimal places, using the number format "#,##0.00". If that's what you are looking for, you can adapt it to sum every 22nd cell in col C if that's still your objective.

Book1
	F	G	H	I	J	K	L
4		Sample1	Sample2	Sample3	Sample3	Sample4	Sample5
5		= 785.000,00	= 1.785.000,00	=10.785.000,00	= 100.785.000,00	= 1.100.785.000,00	= 11.100.785.000,00
6		= 785.000,00	= 1.785.000,00	=10.785.000,00	= 100.785.000,00	= 1.100.785.000,00	= 11.100.785.000,00
7		= 785.000,00	= 1.785.000,00	=10.785.000,00	= 100.785.000,00	= 1.100.785.000,00	= 11.100.785.000,00
8		= 785.000,00	= 1.785.000,00	=10.785.000,00	= 100.785.000,00	= 1.100.785.000,00	= 11.100.785.000,00
9	Sum	3,140,000.00	7,140,000.00	43,140,000.00	403,140,000.00	4,403,140,000.00	44,403,140,000.00
Sheet1

Sub test()
Dim R As Range, Col As Range, c As Range, S As Double, D As Double
Set R = Range("G5:L8")
Application.ScreenUpdating = False
For Each Col In R.Columns
    For Each c In Col.Cells
        If InStrRev(c.Value, ",") = Len(c.Value) - 2 Then ' there's a decimal amount
            D = D + Right(c.Value, 2) + 0
            S = S + Replace(Replace(Replace(Left(c.Value, Len(c.Value) - 3), "=", ""), ".", ""), ",", "") + 0
        Else
            S = S + Replace(Replace(Replace(c.Value, "=", ""), ".", ""), ",", "") + 0
        End If
    Next c
    With Cells(Rows.Count, Col.Column).End(xlUp).Offset(1, 0)
        .Value = S + D / 100
        .NumberFormat = "#,##0.00"
    End With
D = 0: S = 0
Next Col
Application.ScreenUpdating = True
End Sub

 

[muhammad susanto]

OK. Here's some code that carries out the sum to two decimal places, using the number format "#,##0.00". If that's what you are looking for, you can adapt it to sum every 22nd cell in col C if that's still your objective.

Book1
	F	G	H	I	J	K	L
4		Sample1	Sample2	Sample3	Sample3	Sample4	Sample5
5		= 785.000,00	= 1.785.000,00	=10.785.000,00	= 100.785.000,00	= 1.100.785.000,00	= 11.100.785.000,00
6		= 785.000,00	= 1.785.000,00	=10.785.000,00	= 100.785.000,00	= 1.100.785.000,00	= 11.100.785.000,00
7		= 785.000,00	= 1.785.000,00	=10.785.000,00	= 100.785.000,00	= 1.100.785.000,00	= 11.100.785.000,00
8		= 785.000,00	= 1.785.000,00	=10.785.000,00	= 100.785.000,00	= 1.100.785.000,00	= 11.100.785.000,00
9	Sum	3,140,000.00	7,140,000.00	43,140,000.00	403,140,000.00	4,403,140,000.00	44,403,140,000.00
Sheet1

Sub test()
Dim R As Range, Col As Range, c As Range, S As Double, D As Double
Set R = Range("G5:L8")
Application.ScreenUpdating = False
For Each Col In R.Columns
    For Each c In Col.Cells
        If InStrRev(c.Value, ",") = Len(c.Value) - 2 Then ' there's a decimal amount
            D = D + Right(c.Value, 2) + 0
            S = S + Replace(Replace(Replace(Left(c.Value, Len(c.Value) - 3), "=", ""), ".", ""), ",", "") + 0
        Else
            S = S + Replace(Replace(Replace(c.Value, "=", ""), ".", ""), ",", "") + 0
        End If
    Next c
    With Cells(Rows.Count, Col.Column).End(xlUp).Offset(1, 0)
        .Value = S + D / 100
        .NumberFormat = "#,##0.00"
    End With
D = 0: S = 0
Next Col
Application.ScreenUpdating = True
End Sub

related similar problem this link VBA : Sum Every 22 Rows in a Sheet with Data Not Fully Number