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