Sum two or three different values in a cell differentiated with an alphabet and a comma in a cell

[anne7808]

Hi,
I wanted the sum total of different nos. separated by a comma in a row of cells in the exact order with commas in another cell
and also the alphabets written along with the nos. just appear as the normal letter alongside the total number.

like = _V, _S, _CC

 

[anne7808]

Alright, I can probably come up with something that you can easily work with and/or change later on, but I am unclear about one thing. In your example you said you got this...

400 V, 200 M

from this....

2 V, 1 M

(there are spaces between the numbers and letters, correct...your example did not show them?) So in that example, each number was multiplied by the same amount (200)... will that always be the case or could some of the numbers in front of a letter be multiplied by different numbers from the rest (that is, say, V numbers are multiplied by 150 and M numbers by 85)?

Hi. Alternatively, I thought of a simpler way for my calculations.
Could you tell me how would it be possible to extract all the numbers assigned to letters into different cells (eg. {2V, 4M, 6SS} into 3 cells - '2', '4' '6' )
so I can use these only for normal multiplying formula and add it later.

This would eliminate the need to multiply first and then extract it to calculate the sum.

thank you

 

[anne7808]

Assuming that if any of your numbers are floating point numbers, then the decimal point is a dot and the decimal portion of the number never ends with trailing zeroes (that is, you never have a value like 12.30V, rather, it is always presented as 12.3V), then this UDF (user defined function) should work for you...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function LetterSum(Rng As Range) As String
  Dim X As Long, Num As String, Letter As String, V As Variant, Parts() As String
  Parts = Split(Replace(Join(Application.Index(Rng.Value, 1, 0), ","), " ", ""), ",")
  With CreateObject("Scripting.Dictionary")
    For X = 0 To UBound(Parts)
      If Len(Parts(X)) Then
        Num = Val(Parts(X))
        Letter = Mid(Parts(X), Len(Num) + 1)
        .Item(Letter) = .Item(Letter) + Val(Num)
      End If
    Next
    For Each V In .keys
      LetterSum = LetterSum & ", " & .Item(V) & " " & V
    Next
  End With
  LetterSum = Mid(LetterSum, 3)
End Function[/TD]
[/TR]
</tbody>[/TABLE]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use LetterSum just like it was a built-in Excel function. For example,

=LetterSum(B2:AF2)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Hello sir, I had another query regarding this VBA. I needed a sum total of all the cells which were done in a column with this special formula "LetterSum". The grand total comes as the first cell only and it doesn't sum up all the cells with that special formula. I hope you understood my problem.

For e.g. Three cells have the sum total of their row like (1 V ,1 M); (2 V,2 M); (3 V, 3 M) {using the formula of the given VBA}
SO when I use letter sum in a cell to give the sum total of these cells, I get the result (1 V, 1 M) and not the actual total which would be (6 V, 6 M)

 

[anne7808]

Hi Sir, you posted this UDF a while ago for my question. Is there any way how to use "LETTERSUM" vertically? It seems to only show the first cell no. when used vertically.

Assuming that if any of your numbers are floating point numbers, then the decimal point is a dot and the decimal portion of the number never ends with trailing zeroes (that is, you never have a value like 12.30V, rather, it is always presented as 12.3V), then this UDF (user defined function) should work for you...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function LetterSum(Rng As Range) As String
  Dim X As Long, Num As String, Letter As String, V As Variant, Parts() As String
  Parts = Split(Replace(Join(Application.Index(Rng.Value, 1, 0), ","), " ", ""), ",")
  With CreateObject("Scripting.Dictionary")
    For X = 0 To UBound(Parts)
      If Len(Parts(X)) Then
        Num = Val(Parts(X))
        Letter = Mid(Parts(X), Len(Num) + 1)
        .Item(Letter) = .Item(Letter) + Val(Num)
      End If
    Next
    For Each V In .keys
      LetterSum = LetterSum & ", " & .Item(V) & " " & V
    Next
  End With
  LetterSum = Mid(LetterSum, 3)
End Function[/TD]
[/TR]
</tbody>[/TABLE]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use LetterSum just like it was a built-in Excel function. For example,

=LetterSum(B2:AF2)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.