summing comma separated values in a range
 

Just try to explain what it does that you don't want or vice versa/

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" wrote in message
...

You shouldn't get rid of the hyphens, they are intrinsic to the
solution.

The number of CSV is dependant on the range size, it would be simple
to
define a named range, and use that name in the formula.

I don't know about typically, but here SUBSTITUTE is used to replace
an
instance of the comma with a ~ for uniqueness which FIND will locate,
thereby getting the comma's position. This is passed to the MID
function

to

extract the value between two commas. SUMPRODUCT Then sums across the

range

that location.

The -- is used for coercing Booleans to a number. See
http://www.xldynamic.com/source/xld.SUMPRODUCT.html for details.

I don't know why it is not working for you. I can assure you that it
does
work. I created test data as presented by you, and got the results you

gave,

albeit in separate cells.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"bthoron" wrote in message
oups.com...

Bob- this didn't work out very well for me, I think I got rid of the
hyphens, but all I get is #value.
The other thing I note about the formula method is that it limits
the
number of csv to the number in the formula. The code above could
handle any number of csv (provided the entire range had the same)

I'm curious about --MID and Substitute how are these used typically?

Thanks