sumproduct of part cells of a range with blanks

[Guest]

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

 

[Peo Sjoblom]

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

 

[Guest]

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]

I would use a formula to extract all numbers in one column,

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

copy down

then use

=SUMPRODUCT(--(ISNUMBER(FIND("a",A5:A10)),B5:B10))

will sum extracted numbers in B where A is "a"

 

[Guest]

Many thanks Peo Sjoblom for you great help
your second suggestion for summing for a particular letter is very helpfull
many thanks again and again