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

 

[muhammad susanto]

here my sample in google drive for easy to try

excel sum string_okxlsx.xlsx

drive.google.com

 

[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

sorry maybe not clear, my expected value in cell G10.. The core is how to sum data from cell G3 till G9. the data contains number, mark =, comma, dot , space

 

[muhammad susanto]

similar problem, cross post Summing Not Fully Number from Multiple Sheet in cell C21

 

[muhammad susanto]

the code not working, maybe someone to fix it.
after run the code the result is 0

Sub AddCells()
Dim l, j As Long, w As Worksheet

Application.ScreenUpdating = False

j = 0
For Each w In ActiveWorkbook.Sheets
On Error Resume Next
    w.Select
    j = CLng(Replace(Replace(Replace(w.Range("C21").Value, "=", ""), ".", ""), ",", ".")) + j
Next
   MsgBox j
   
Application.ScreenUpdating = True
   
End Sub

note: the value content excessice spaces , 1 spaces before mark "=" and 1 spaces after mark "="

 

[JoeMo]

the code not working, maybe someone to fix it.
after run the code the result is 0

Sub AddCells()
Dim l, j As Long, w As Worksheet

Application.ScreenUpdating = False

j = 0
For Each w In ActiveWorkbook.Sheets
On Error Resume Next
    w.Select
    j = CLng(Replace(Replace(Replace(w.Range("C21").Value, "=", ""), ".", ""), ",", ".")) + j
Next
   MsgBox j
  
Application.ScreenUpdating = True
  
End Sub

note: the value content excessice spaces , 1 spaces before mark "=" and 1 spaces after mark "="

Not sure I understand what you want. The code below will sum the range G3:G9 on every worksheet and produce the aggregate sum for all worksheets. Assumes there are no errors or text not conforming to the format you show in any of the cells included in the ranges to be summed.

Sub TextToNumbersSum()
Dim w As Worksheet, S As Double
For Each w In ActiveWorkbook.Worksheets
    For Each c In w.Range("G3:G9")
        S = S + Replace(Replace(Replace(c.Value, "=", ""), ".", ""), ",", "") + 0
    Next c
Next w
MsgBox Format(S, "#,##0")
End Sub

 

[muhammad susanto]

hi JoeMo, working well, that's i want it..
THANK YOU SO MUCH....

 

[muhammad susanto]

Not sure I understand what you want. The code below will sum the range G3:G9 on every worksheet and produce the aggregate sum for all worksheets. Assumes there are no errors or text not conforming to the format you show in any of the cells included in the ranges to be summed.

Sub TextToNumbersSum()
Dim w As Worksheet, S As Double
For Each w In ActiveWorkbook.Worksheets
    For Each c In w.Range("G3:G9")
        S = S + Replace(Replace(Replace(c.Value, "=", ""), ".", ""), ",", "") + 0
    Next c
Next w
MsgBox Format(S, "#,##0")
End Sub

ho JoeMo, i found new problem for this format MsgBox Format(S, "#,##0"), if the number format like eg. 877.000 or 1.500.999 or 22.100.000 or 150.750.000 or 1.165.700.000 or 12.500.600.000, how to modified Format Number like that

 

[JoeMo]

ho JoeMo, i found new problem for this format MsgBox Format(S, "#,##0"), if the number format like eg. 877.000 or 1.500.999 or 22.100.000 or 150.750.000 or 1.165.700.000 or 12.500.600.000, how to modified Format Number like that

You can change the format in the message box to whatever you want. I thought you want to remove all the "." characters. What number would you convert say 12.500.600.000 to??

 

[muhammad susanto]

You can change the format in the message box to whatever you want. I thought you want to remove all the "." characters. What number would you convert say 12.500.600.000 to??

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
        Cells(r, 3).Value = 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