You can convert to number by doing a math operation on the cell. Easiest way
is to enter 1 in a cell, copy it (ctrl-c), select your range of text
00001345 numbers, and go to editpaste specialmultiply. (you could also
copy a 0 and use paste specialadd or subtract). This will convert them to
numbers.

=SUMPRODUCT(--(--LEFT(--A1:A8,2)=13),(M1:M8))

(based on info on your follow-up post). This would work whether or not you
convert these text to numbers with the paste special method above.. the --
before LEFT converts text to number.




"Ket" wrote in message
...
Hello,

I have a worksheet where column A contains reference numbers, eg
000001346, 000001347, 000001542 etc.
What formula can I create in column B that will find all entries in
column A that begin with 0000013 and add them up?

The other factor is that this data is imported from an external system
so that all the reference numbers are currently recognised as text. Is
there an easy way to convert these to numbers? Presently I am having
to right click each cell individually and choosing 'Convert to number'

I am using Excel 2003.

Thanks in advance for your help.

Ket
London