How to sum the total for 1(1A) + 3(3A) + 5 = 9(4A) ?

[jcmt427]

How to sum the total for 1(1A) + 3(3A) + 5 = 9(4A) ?

other examples:

1(1A) + 3(2A) = 4(3A)

10(2A) + 1 = 11(2A)

I need to sum at least 100 cells in the Excel (same column or same row).

Thank you.

 

[Marius44]

Hello
Try this macro

Sub SUM_TEXT()
Dim ur As Long, i As Long, nr, n1, n2
ur = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To ur
  On Error Resume Next
  nr = Split(Cells(i, 1), "(")
  If UBound(nr) > 0 Then
    n2 = n2 + Val(nr(1))
  End If
  On Error GoTo 0
  n1 = n1 + Val(nr(0))
Next i
Cells(2, 3) = n1 & "(" & n2 & Right(nr(1), 2)
End Sub

Let me know. Hello,
Mario

 

[jcmt427]

Many thanks for your swift response, it works

just one more question, is it possible to use formula to get the same answer as the code above ?

Using Sum Function how to sum only the number if joined text as its own criteria

Hi Guys, Here´s the challenge using Sum Function how to sum only the number if joined text as its own criteria. Accordingly with table below the goal its to sum only numbers joined with letter "A" so sum the numbers in customers columns like: "1A"; "3A" and "2A" which result will be 6. Thank...

www.mrexcel.com

Thank you very much in advance.

 

[Marius44]

Hello
The formula in ITALIAN is the following:
=MATR.SOMMA.PRODOTTO(SINISTRA(A1:A5;SE.ERRORE(TROVA("(";A1:A5)-1;1))*1)&"("&MATR.SOMMA.PRODOTTO(SOSTITUISCI(SE.ERRORE(STRINGA.ESTRAI(A1:A5;TROVA("(";A1:A5)+1;100);0);"B)";"")*1)&"B)"

which I tried to "translate" like this:
=SUMPRODUCT(LEFT(A1:A5,IFERROR(FIND("(",A1:A5)-1,1))*1&"("&SUMPRODUCT(SUBSTITUTE(IF ERROR(MID(A1:A5,FIND("(",C2:C9)+1,100),0),"B)","")*1)&"B)"

Keep in mind two things:
1 °) is a matrix formula to be confirmed with Ctrl + Shift + Enter
2 °) the ";" in Italian it must be changed to ","

Hope this helps.
Hello,
Mario

 

[jcmt427]

Hello
The formula in ITALIAN is the following:
=MATR.SOMMA.PRODOTTO(SINISTRA(A1:A5;SE.ERRORE(TROVA("(";A1:A5)-1;1))*1)&"("&MATR.SOMMA.PRODOTTO(SOSTITUISCI(SE.ERRORE(STRINGA.ESTRAI(A1:A5;TROVA("(";A1:A5)+1;100);0);"B)";"")*1)&"B)"

which I tried to "translate" like this:
=SUMPRODUCT(LEFT(A1:A5,IFERROR(FIND("(",A1:A5)-1,1))*1&"("&SUMPRODUCT(SUBSTITUTE(IF ERROR(MID(A1:A5,FIND("(",C2:C9)+1,100),0),"B)","")*1)&"B)"

Keep in mind two things:
1 °) is a matrix formula to be confirmed with Ctrl + Shift + Enter
2 °) the ";" in Italian it must be changed to ","

Hope this helps.
Hello,
Mario

Thank you so much for your kind assistance