Adding Cells

[Ket]

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

 

[Ket]

Apologies, should have stated that the formula needs to find all
entries in column A that begin with 0000013 and then add up the
corresponding cells in column M.

 

[JulieD]

Hi Ket

that makes it a bit easier

=SUMIF(A:A,"0000013*",M:M)

should give you what you're after

Cheers
JulieD

 

[N Harkawat]

=SUMIF(A1:A80,"0000013*",m1:m80)

Apologies, should have stated that the formula needs to find all
entries in column A that begin with 0000013 and then add up the
corresponding cells in column M.

 

[Dave R.]

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 edit>paste special>multiply. (you could also
copy a 0 and use paste special>add 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]

Thankyou!

Hi Ket

that makes it a bit easier

=SUMIF(A:A,"0000013*",M:M)

should give you what you're after

Cheers
JulieD

 

[JulieD]

you're welcome

Thankyou!