spit & sum comma delimted value
Thanks Rick.
Works 100%.
"Rick Rothstein" wrote:
Are you allowed to use VBA code? If so, this User Defined Function (UDF) can
do what you want...
Function CommaSum(R As Range) As Double
Dim X As Long
Dim Parts() As String
Parts = Split(R.Value, ",")
For X = 0 To UBound(Parts)
CommaSum = CommaSum + Parts(X)
Next
End Function
To install it, press Alt+F11 to get into the VBA editor; once there, click
Insert/Module from its menu bar; then copy/paste the above code into the
code window that opened when you did that. You are done. Go back to your
worksheet and, assuming your values are in A1, A2, etc., enter this formula
in, say, B1...
=CommaSum(A1)
and copy it down. You can use the CommaSum function inside other functions
just like you can do with the built-in worksheet functions.
--
Rick (MVP - Excel)
"Francois Taljaard" wrote in
message ...
Did try it the cell is actually a vlookup and the formula much run in
a If formula.
The data is not consistent it might look something like
20,400,20,30 = 470.
Use the if statement to check for "=" if true I do a Mid to get the value
(470),
if false I need to sum (20,400,20,30).
Cell:
=VLOOKUP(Table_Default__ORDERBOM[[#This Row],[PRODUCT]],PRODUCTS!A:G,7)
|