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

this related in my posting in VBA : Sum Every 22 Rows in a Sheet with Data Not Fully Number
i have try modify your code like this:

Sub TextToNumbersSum()
Dim w As Worksheet, S As Double
lr = Cells(Rows.Count, "C").End(xlUp).Row
S = 0
    For r = 21 To lr Step 22
       [B] Cells(r, 3).Value [/B]= Replace(Replace(Replace(Cells(r, 3).Value, "=", ""), ".", ""), ",", "")
        S = S + Cells(r, 3).Value
    Next r
MsgBox Format(S, "#,##0")
End Sub

or summing is correct but the mark of number is gone like mark =,comma,dot, not dissapear again
i want the mark is not gone, please see this picture, before and after run macro

The marks disappear because you set the value (in bold above) to the replacement.

See if this works for you:

Sub TextToNumbersSum()
Dim w As Worksheet, S As Double
lR = Cells(Rows.Count, "C").End(xlUp).Row
For r = 21 To lR Step 22
    S = S + Replace(Replace(Replace(Cells(r, 3).Value, "=", ""), ".", ""), ",", "") + 0
Next r
MsgBox Format(S, "#,##0")
End Sub

 

[muhammad susanto]

hi

The marks disappear because you set the value (in bold above) to the replacement.

See if this works for you:

Sub TextToNumbersSum()
Dim w As Worksheet, S As Double
lR = Cells(Rows.Count, "C").End(xlUp).Row
For r = 21 To lR Step 22
    S = S + Replace(Replace(Replace(Cells(r, 3).Value, "=", ""), ".", ""), ",", "") + 0
Next r
MsgBox Format(S, "#,##0")
End Sub

hi Joe, still not full work i mean for summing have problem, after run macro, the result not correct over 2 digits number in behind (zero number)

 

[JoeMo]

hi

hi Joe, still not full work i mean for summing have problem, after run macro, the result not correct over 2 digits number in behind (zero number)

Like to help, but I have no idea what your data and data layout looks like. Can you post a sample and show the result you get and what your expected result is?

 

[muhammad susanto]

Like to help, but I have no idea what your data and data layout looks like. Can you post a sample and show the result you get and what your expected result is?

hi..
this my sample assuming start from c21,....

Asumming	Sample1	Sample2	Sample3	Sample3	Sample4	Sample5
in cell C21	= 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
cell C43	= 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
cell C65	= 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
cell C87	= 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
Expected (SUM)	3.140.000	7.140.000	43.140.000	403.140.000	4.403.140.000	44.403.140.000

note :
my target in line "expected (sum)" in one sheet not multiple sheet, and the result in Indonesia Format Number

 

[JoeMo]

hi..
this my sample assuming start from c21,....

Asumming	Sample1	Sample2	Sample3	Sample3	Sample4	Sample5
in cell C21	= 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
cell C43	= 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
cell C65	= 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
cell C87	= 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
Expected (SUM)	3.140.000	7.140.000	43.140.000	403.140.000	4.403.140.000	44.403.140.000

note :
my target in line "expected (sum)" in one sheet not multiple sheet, and the result in Indonesia Format Number

I can't test this because I don't have access to your regional settings. Try changing this:

Format(S, "#,##0")
to this:
Format(S, "#.##0")
in the line that has the message box.
You would need to add a loop for the other columns beyond column C.

 

[muhammad susanto]

I can't test this because I don't have access to your regional settings. Try changing this:

Format(S, "#,##0")
to this:
Format(S, "#.##0")
in the line that has the message box.
You would need to add a loop for the other columns beyond column C.

hi.. i have doing your instruction and trying to change format number with several combination but the result of sum is wrong. maybe this not only about format number digits, or there some code missing it.

 

[JoeMo]

hi.. i have doing your instruction and trying to change format number with several combination but the result of sum is wrong. maybe this not only about format number digits, or there some code missing it.

Can you post an example of where sum is wrong using XL2BB so I can copy the data easily? Also, ignoring the accuracy of the sum, is it formatted the way you want it to be?

 

[muhammad susanto]

Can you post an example of where sum is wrong using XL2BB so I can copy the data easily? Also, ignoring the accuracy of the sum, is it formatted the way you want it to be?

hi...this my expected result in table and i attachment "sum of result" as picture after using your macro code

Book1
	F	G	H	I	J	K	L
3	Asumming	Sample1	Sample2	Sample3	Sample3	Sample4	Sample5
4	in cell C21	= 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
5	cell C43	= 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	cell C65	= 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	cell C87	= 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	My Expected (SUM - Correct)	3,140,000	7,140,000	43,140,000	403,140,000	4,403,140,000	44,403,140,000
9	Sum Using Your Code (Not Correct)	314,000,000,000	714,000,000,000	4,314,000,000,000	etc.	etc.	etc.
Sheet1

 

[JoeMo]

Do your regional settings use "." as the decimal separator and "," as the thousands separator? Will there always be only two digits after the last ","?

 

[muhammad susanto]

Do your regional settings use "." as the decimal separator and "," as the thousands separator? Will there always be only two digits after the last ","?

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.