thanks Peo Sjoblom for your help your suggestion works very nice
but now if you can help please,
i like to sum for a particular letter for examlpe those of .a only
many thanks again

"Peo Sjoblom" wrote:

It's not that it is a blank per se, it's because find cannot find a period,
this should work
unless you have letter with periods like aaa.a but if the only other
alternative to numberDOTletter
is a blank this should work

=SUM(IF(ISERROR(FIND(".",A5:A10)),0,--LEFT(A5:A10,FIND(".",A5:A10))))

entered with ctrl + shift & enter

--

Regards,

Peo Sjoblom


"excelFan" wrote in message
...
hi all,
please help
REF is the name of the range A5:A10, it's items are the following
A5=123.a
A6=45.b
A7=22.b
A8=19.a
A9=25.a
A10 is blank
the formula =sumproduct(--left(REF,find(".",REF)-1)) gives an error
#VALUE! if any cell of the range is blank.
is there a way to solve this problem
with many thanks